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:
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;
You can use
LOCATE() – as in the other answer – or
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 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'
LIKE is not case sensitive. If you want to do a case sensitive search, replace
SELECT * FROM site_core_antispam_banned_domain WHERE RIGHT(domain_name,10)='domain.com';