SQL Query like that: "some string" LIKE "%"+column+"%"

Question

I've got a table full of substrings, and I need to select all substrings, which are in the search string.

Basically it would be the mirrored form of a where-condition:

WHERE "SearchString" LIKE "%"+ currentColumnValue +"%"

I know, that this is not possible, but for performance reasons I don't want to iterate every single database entry.

Maybe you have got an idea how to solve this kind of problem?

Why do you think it's not possible?

Aleks G
– Aleks G

2014-12-10 16:45:33 +00:00
Commented Dec 10, 2014 at 16:45 — Aleks G
– Aleks G, Commented Dec 10, 2014 at 16:45

Andreas Wederbrand · Accepted Answer · 2014-12-10 16:50:59Z

3

You can do

where 'SearchString' like concat('%', columnValue, '%');

This will be very slow as it would do a table scan and a like-compare on each line, but the result is correct.

http://sqlfiddle.com/#!2/e2b066/1

edited Dec 10, 2014 at 16:50

answered Dec 10, 2014 at 16:45

Andreas Wederbrand

40.5k12 gold badges71 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert Over a year ago

Awesome, that's it! What do you think? Is it better to perform this task on MySQL-side or on PHP-side?

Andreas Wederbrand Over a year ago

It's better on the MySQL side. If you're doing it in PHP you basically have to read the entire table into PHP and filter each row. The added cost is the network traffic that happens before the filter. In MySQL you still have to filter on each row but there is no network for rows not matching.

jay.jivani · Accepted Answer · 2014-12-10 16:54:57Z

0

You can try this

$value = //your value

' select * from tb_name where col_name like %".$value."% '

answered Dec 10, 2014 at 16:54

jay.jivani

1,5741 gold badge19 silver badges33 bronze badges

1 Comment

Robert Over a year ago

Sorry, you did not understand my question. That's the normal way of searching. I'm not searching for a row which contains my search string, but for a row which value is a substring of my (much longer) search string.

Collectives™ on Stack Overflow

SQL Query like that: "some string" LIKE "%"+column+"%"

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related