Keith C. Keith C. - 1 month ago 10
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:
"www.example.com/test"

My database column urlColumn contains:
"www.example.com"

If I do the following (pseudo):

SELECT * FROM db WHERE urlColumn LIKE '%www.example.com/test%'


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

SELECT * FROM db WHERE urlColumn LIKE '%www.example.com%'


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 'www.exmaple.com/test'

Answer

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

SELECT * FROM db WHERE 'www.example.com/test' LIKE CONCAT('%',urlColumn,'%');

More:

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 := 'www.example.com';
SET @needle := 'www.example.com/test';

SELECT 

IF(LENGTH(@hayStack) > LENGTH(@needle), 
    @hayStack LIKE CONCAT('%',@needle,'%'),
    @needle LIKE CONCAT('%',@hayStack,'%')
)
Comments