LIKE 查询 sql 不适用于带空格的串联值 [英] LIKE query sql not working in concatenated values with space

问题描述

我有这张桌子

**

--------------------------------------------------
| id    | fname       | lname      | age
--------------------------------------------------
| 1     | John        | Smith      | 20
-------------------------------------------------
| 2     | John Craig  | Smith      | 20
-------------------------------------------------- 
| 3     | John Shaw   | Smith      | 20
--------------------------------------------------

MYSQL 查询:

select id from person where concat(fname, lname) LIKE = '%johnsmith%' - 这可以

选择id但如果姓氏中有两个这样的词:

select the id but if there are two words in last name like this:

select id from person where concat(fname, lname) LIKE = '%johncraigsmith%'

它不会显示任何结果.

为什么?你能帮我吗?

推荐答案

因为 john 和 craig 之间有一个空格.那行得通

Because you have a space between john and craig. That would work

select id from person 
where replace(concat(fname, lname),' ','') LIKE = '%johncraigsmith%'

但这对性能来说太糟糕了.最好是

but that is terrible on performance BTW. Better would be

select id from person 
where lname = 'smith'
and fname = 'john craig'

这篇关于LIKE 查询 sql 不适用于带空格的串联值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！