How to make the column wildcard based on the entry?

Question

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 ?

LukStorms · Accepted Answer · 2021-12-19T13:50:23.623

2

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

edited Dec 19 '21 at 13:50

answered Dec 19 '21 at 12:20

LukStorms

28,916
5
31
45

Is any approach to take words apart based on the space using MySQL ? – Martin AJ Dec 19 '21 at 12:46
You mean something like [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql) in MS Sql Server that splits a character seperated string to rows? Then no, by my knowledge, MySql doesn't have something like that. And a MySql function can't return a table, only procedures can. But some complicated tricks could work. F. e. [here](https://stackoverflow.com/a/45761467/4003419) – LukStorms Dec 19 '21 at 13:11
Well, I need to make `IN ('John','Foo')` according to "John Foo" string somehow to take advantage of indexes *(as you mentioned)* – Martin AJ Dec 19 '21 at 13:15
See addition. It assumes there's not more than 3 parts to a name though. – LukStorms Dec 19 '21 at 13:51

score 1 · Answer 2 · answered Dec 19 '21 at 12:10

1

You might be able to use LOCATE().

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

answered Dec 19 '21 at 12:10

Jim Jimson

2,368
3
17
40

How to make the column wildcard based on the entry?

2 Answers2