Match both www.website.com and website.com with sql like

Question

I have a table that contains a list of websites.

I have a sql variable @url, I want to select all the rows where the website is equal to @url. However, if there is no subdomain I want to match www and the root domain.

So, if @url='http://website.com' it should match both 'http://website.com' and 'http://www.website.com'

I'm currently doing

select * from websites a
where b.archived = 0
and @url like replace(a.WebsiteURL, '*', '%')
order by newid()

Obviously that doesn't take into account the www issue.

Any ideas how to solve this problem?

I was thinking of force adding the www. if it doesn't exist (and the url doesn't have a subdomain) before adding it to the DB. But, what about websites that actually don't have the www and redirect the www to the root?

Answer 1

You should be using the RegexMatch (or similar) feature of SQL. With a regex test of "http://(?:www.)?whatever.com", you should match both "http://whatever.com" and "http://www.whatever.com".

Answer 2

  http://website.com
   http://www.website.com

Reverse the strings

moc.etisbew//:ptth
moc.etisbew.www//:ptth

-- Do this once to the input string

Search Str = replace(replace(Str,'www',''),'http://')

Reverse the input string as well

moc.etisbew

Now you can search and use an index. You could create an update trigger to automatically populate the reverse field, which is never shown to the user, only an indexable field.

Little strange, but should give you pretty good lookup performance

Answer 3

You could divide WebsiteURL column into WebsiteScheme, WebsiteSubdomain, WebsiteDomain columns. Thus the check will turn to:

@url = WebsiteScheme + '://' + WebsiteSubdomain + '.' + WebsiteDomain OR 
@url = WebsiteScheme + '://' + WebsiteDomain AND WebsiteSubdomain = 'www'