Find if any of the rows partially match a string

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;

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

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.

StackExchange Database Administrators Q#203206, answer score: 13