在SQL中为WHERE子句合并两列 [英] Combine two columns in SQL for WHERE clause

问题描述

在我的SQL中，我使用WHERE和LIKE子句执行搜索.但是，我需要对两列-first_name和last_name的组合值执行搜索:

In my SQL, I am using the WHERE and LIKE clauses to perform a search. However, I need to perform the search on a combined value of two columns - first_name and last_name:

WHERE customers.first_name + customers.last_name LIKE '%John Smith%'

这行不通，但是我想知道如何按照这些思路做些什么?

This doesn't work, but I wondered how I could do something along these lines?

我试图按两列来分开搜索，就像这样:

I have tried to do seperate the search by the two columns, like so:

WHERE customers.first_name LIKE '%John Smith%' OR customers.last_name LIKE '%John Smith%'

但是显然，这是行不通的，因为搜索查询是这两列的组合值.

But obviously that will not work, because the search query is the combined value of these two columns.

推荐答案

使用以下内容:

WHERE CONCAT(customers.first_name, ' ', customers.last_name) LIKE '%John Smith%'

请注意，为了使此功能按预期工作，应修剪名字和姓氏，即，它们不应包含前导或尾随空格.在插入数据库之前，最好在PHP中修剪字符串.但是，您也可以像这样将修剪合并到您的查询中:

Note that in order for this to work as intended, first name and last name should be trimmed, i.e. they should not contain leading or trailing whitespaces. It's better to trim strings in PHP, before inserting to the database. But you can also incorporate trimming into your query like this:

WHERE CONCAT(TRIM(customers.first_name), ' ', TRIM(customers.last_name)) LIKE '%John Smith%'

这篇关于在SQL中为WHERE子句合并两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！