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 "domain.com" as it is a substring of "somedomain.com".

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`)

4 Answers 4


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

FROM site_core_antispam_banned_domain
WHERE 'subdomain.com' 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 somedomain.com.ro and you were only interested in *.com.ro results), you could make the pattern more specific:

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

Note: there is no performance guarantee with these queries. The execution plan will likely include either a full index scan - if there is an index on (domain_name) - or a full table scan.

  • I didn't know is possible with like. Its perfect as its much faster then locate:)
    – Emanuel
    Commented Apr 6, 2018 at 7:14
  • Why the CONCAT? Doesn't LIKE '%domain.com%' work just as well (or better)?
    – Vérace
    Commented Apr 6, 2018 at 10:45
  • 2
    @Verace 'domain.com' is the value in the column domain_name. It will be in the results. 'subdomain.com' is the parameter/string we are looking for (and all its substrings) Commented Apr 6, 2018 at 10:58

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,'subdomain.com')!=0;

Hope this helps.


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

SELECT domain FROM mytable WHERE domain LIKE '%domain.com%';

This will do what you asked. But in your examples "domain.com" 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)='domain.com';
  • I dont see how this can work in my case. I need to find all records that are substrings of the given string.
    – Emanuel
    Commented Apr 6, 2018 at 6:13
  • Add your table structure and your sample output Commented Apr 6, 2018 at 6:19
  • 1
    You've got it backwards. The OP has a string and wants to find values that are included in that string. But you have a string and are trying to find values that include that string.
    – Andriy M
    Commented Apr 6, 2018 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.