5

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

Let's say I have a string

somedomain.com 

In database I have:

blabladomain.com
testdomain.com
domain.com

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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
8

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

SELECT * 
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:

SELECT * 
FROM site_core_antispam_banned_domain
WHERE 'subdomain.com' LIKE CONCAT('%', domain_name) ;
  • I didn't know is possible with like. Its perfect as its much faster then locate:) – Emanuel Apr 6 '18 at 7:14
  • Why the CONCAT? Doesn't LIKE '%domain.com%' work just as well (or better)? – Vérace Apr 6 '18 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) – ypercubeᵀᴹ Apr 6 '18 at 10:58
3

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.

  • No Problemo and always happy to help :) – Tayyab Apr 6 '18 at 6:35
2

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.

2

Try it:

 SELECT * 
 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 Apr 6 '18 at 6:13
  • Add your table structure and your sample output – Pankaj Kumar Apr 6 '18 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 Apr 6 '18 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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