Inverse of SQL LIKE '%value%'

Question

I have a MySQL table containing domain names:

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

I'd like to be able to search through this table for a full hostname (i.e. 'www.google.com'). If it were the other way round where the table contained the full URL I'd use:

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

But the inverse is not so straightforward. My current thinking is to search for the full hostname, then progressively strip off each part of the domain, and search again. (i.e. search for 'www.google.com' then 'google.com')

This is not particular efficient or clever, there must be a better way. I am sure it is a common problem, and no doubt easy to solve!

Answer 1

You can use the column on the right of the like too:

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

or

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

It's not particularly efficient but it works.

Answer 2

Sorry, this isn't an answer, just a request for clarification.

Do you want to match www.google.com, but not google.com? Because %.google.com% will match all, I think.

Answer 3

You could use a bit of SQL string 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]

Answer 4

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

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

It appears that the way RLIKE has been implemented it is even smart enough to treat the dot in that field (normally a wildcard in regex) as a literal dot.

MySQL's inclusion of regular expressions gives you a very powerful ability to parse and search strings. If you would like to know more about regular expressions, just google "regex". You can also use one of these links: http://en.wikipedia.org/wiki/Regular_expression http://www.regular-expressions.info/ http://www.codeproject.com/KB/string/re.aspx