How to make the column wildcard based on the entry?-CodePudding

As you know, if you use .. where col_name like "%word%", then it will be searching as wildcard. Ok, all is file. Now I want exactly the vice versa. I mean, I want to make the column wildcard based on the entry.

Please take a look at this:

// tb_name
 -------------- 
|   col_name   |
 -------------- 
| Ali          |
| Martin       |
| John         |
 --------------

I want to match the third row by this value: John Foo. Or match the first row by this entry: Mr Ali. So conceptically I want something like this: .. where %col_name% like "word". How can I do that in MySQL ?

CodePudding user response：

You stich the wildcards % to the col_name.

Then you can like John Foo.

select *
from tb_name 
where 'John Foo' like concat('%',col_name,'%')

But if col_name is indexed, then using IN will be faster.
Because concat('%',col_name,'%') isn't sargable.

select *
from tb_name 
where col_name IN ('John','Foo')

Or the more complicated way, by getting the parts from the name string.

select t.*
from tb_name t
cross join (select 'John Foo Bar' name) names
where t.col_name IN (
           substring_index(name,' ',1), 
           substring_index(substring_index(name,' ', 2),' ',-1), 
           substring_index(substring_index(name,' ', 3),' ',-1)
          )

Demo on db<>fiddle here

CodePudding user response：

You might be able to use LOCATE().

SELECT * FROM tb_name WHERE LOCATE(name, 'John Foo') > 0