Reverse SQL LIKE '% value%'

Question

I have a MySQL table containing domain names:

+----+---------------+ | id | domain | +----+---------------+ | 1 | amazon.com | | 2 | google.com | | 3 | microsoft.com | | | ... | +----+---------------+ 

I would like to be able to search in this table for the full host name (ie "www.google.com"). If it were the other way around, where the table contained the full URL that I would use:

 SELECT * FROM table WHERE domain LIKE '%google.com%' 

But the opposite is not so simple. My current thinking is to search for the fully qualified host name, then gradually remove each part of the domain and search again. (i.e. search for "www.google.com" and then "google.com")

This is not particularly effective or smart; there must be a better way. I am sure this is a common problem, and no doubt it is easy to solve!

Answer 1

You can also use the column to the right of it:

 SELECT domain FROM table WHERE 'www.google.com' LIKE CONCAT('%', domain); 

or

 SELECT domain FROM table WHERE 'www.google.com' LIKE CONCAT('%', domain, '%'); 

This is not particularly effective, but it works.

Answer 2

In mysql you can use regular expressions ( RLIKE ) to do matches. Given this ability, you can do something like this:

 SELECT * FROM table WHERE 'www.google.com' RLIKE domain; 

It looks like the RLIKE path was implemented, it is even smart enough to treat a point in this field (usually a wildcard in regex ) as a literal point.

MySQL's inclusion of regular expressions gives you a very powerful ability to parse and search strings. If you want to know more about regular expressions, just hit " regex ". You can also use one of the following links:

http://en.wikipedia.org/wiki/Regular_expression

http://www.regular-expressions.info/

http://www.codeproject.com/KB/string/re.aspx

Answer 3

You can use a little SQL manipulation to generate the equivalent of string.EndsWith ():

 SELECT * FROM table WHERE substring('www.google.com', len('www.google.com') - len([domain]) , len([domain])+1) = [domain]