I've got a MySQL table that contains domains:
+----+---------------+ | id | domain | +----+---------------+ | 1 | amazon.com | | 2 | google.com | | 3 | microsoft.com | | | ... | +----+---------------+
Let me have the ability to sort through this table for any full hostname (i.e. 'www.google.com'). Whether it were the opposite way round in which the table contained the entire URL I'd use:
SELECT * FROM table WHERE domain LIKE '%google.com%'
However the inverse isn't so straightforward. My current thinking is to find the entire hostname, then progressively remove each area of the domain, and check again. (i.e. look for 'www.google.com' then 'google.com')
This isn't particular efficient or clever, there has to be an easy method. I am certain it's a prevalent problem, and without doubt simple to solve!
You should use the column around the right from the like too:
SELECT domain FROM table WHERE 'www.google.com' LIKE CONCAT('%', domain);
SELECT domain FROM table WHERE 'www.google.com' LIKE CONCAT('%', domain, '%');
It isn't particularly efficient however it works.
In mysql you should use regular expressions (RLIKE) to do matches. With all this ability you could do this something similar to this:
Choose * FROM table WHERE 'www.google.com' RLIKE domain
It seems the way RLIKE continues to be implemented it's even wise enough to deal with the us dot for the reason that area (ordinarily a wildcard in regex) like a literal us dot.
MySQL's inclusion of standard expressions provides you with a really effective capability to parse and check strings. If you'd like to understand much more about regular expressions, just google "regex". You may also use one of these simple links: http://en.wikipedia.org/wiki/Regular_expression http://www.regular-expressions.info/ http://www.codeproject.com/KB/string/re.aspx
Sorry, this is not a solution, only a request clarification.
Would you like to match www.google.com, although not google.com? Because %.google.com% will match all, I believe.
You could utilize a little of SQL string manipulation to create the same as string.EndsWith():
SELECT * FROM table WHERE substring('www.google.com', len('www.google.com') - len([domain]) , len([domain])+1) = [domain]