Find if any of the rows partially match a string

Posted on

Question :

I want to see if a table contains any sub-string of a given string.

Let’s say I have a string 

In database I have:

I need to make a query that will return “” as it is a substring of “”.

I don’t know if this is even possible in MySQL.

CREATE TABLE `site_core_antispam_banned_domain` (
`domain_id` int(11) NOT NULL AUTO_INCREMENT,
`domain_name` varchar(255) NOT NULL,
PRIMARY KEY (`domain_id`)

Answer :

You can use LOCATE() – as in the other answer – or LIKE:

FROM site_core_antispam_banned_domain
WHERE '' LIKE CONCAT('%', domain_name, '%') ;

The above will look for domains that match any part of a given string. If you specifically wanted domains matching the right side of the string (for instance, if the domain to match against was and you were only interested in * results), you could make the pattern more specific:

FROM site_core_antispam_banned_domain
WHERE '' LIKE CONCAT('%', domain_name) ;

I think all you need is Locate function in MySQL as:

SELECT domain_id,domain_name as a 
FROM site_core_antispam_banned_domain 
where Locate(domain_name,'')!=0;

Hope this helps.

Use LIKE. To find a substring located in any part of the string:

SELECT domain FROM mytable WHERE domain LIKE '';

This will do what you asked. But in your examples “” is always at the end of the string. To look for strings ending with a certain substring:

column LIKE '%substring'

Note that LIKE is not case sensitive. If you want to do a case sensitive search, replace LIKE with LIKE BINARY.

Try it:

 FROM site_core_antispam_banned_domain
 WHERE RIGHT(domain_name,10)='';

Leave a Reply

Your email address will not be published. Required fields are marked *