Keith C. Keith C. - 1 year ago 78
MySQL Question

MYSQL Finding substring when column contains needle

How can I find a string in my database if the database contains the needle?

For example I have the string:

My database column urlColumn contains:

If I do the following (pseudo):

SELECT * FROM db WHERE urlColumn LIKE ''

I don't get a result. If I change it to (pseudo):

SELECT * FROM db WHERE urlColumn LIKE ''

It works. Is it true that the element after LIKE must be the needle and not the haystack? Is it possible to do something like this (pseudo):

SELECT * FROM db WHERE %urlColumn% LIKE ''

Answer Source

If the input string can be treated as hayStack then you can use the query given below:

SELECT * FROM db WHERE '' LIKE CONCAT('%',urlColumn,'%');


There's another way you can determine dynamically that which string should be treated as HayStack and which should be treated as Needle.

Here's a simple demonstration:

SET @hayStack := '';
SET @needle := '';


IF(LENGTH(@hayStack) > LENGTH(@needle), 
    @hayStack LIKE CONCAT('%',@needle,'%'),
    @needle LIKE CONCAT('%',@hayStack,'%')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download