CODEWITHSUNDEEP
sqlt-sqlpattern-matchingsql-like
Emmit
Emmit

Reputation: 411

Match against multiple LIKE patterns that come from another query

I have one table with suffixes (TLDs) I am interested in. For example, it contains this:

'.co.uk'
'.de'
'.fr'

I also have another table with URLs. Based on the first table, I want to retrieve all records from the second table that match any of the patterns:

'%.co.uk/%.asp'
'%.de/%.asp'
'%.fr/%.asp'

The general pattern in this case is '%.<TLD>/%.asp'.

How would I do that? Right now I am using a makeshift program that generates a series of ORs based on the first table and I run the generated query. I am not happy with it because the resulting query is enormous and the program has to be rerun every time the TLD table changes.

Upvotes: 0

Views: 54

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Something like this?

It is also generating a list of ORs, but it's doing this dynamically, so there's no need for an external generation and re-generation after changes to your TLDs list. You might think about RegEx, but you'd have a long list too.

One enhancement could be to use a first filter to get all records ending with .asp (fast with RIGHT 4 characters) and then do the pattern search only for your TLDs... 

CREATE TABLE #TLDs(TLD VARCHAR(10));
INSERT INTO #TLDs VALUES('.co.uk'),('.de'),('.fr');

CREATE TABLE #URLs(URL VARCHAR(200));
INSERT INTO #URLs VALUES
 ('http://www.test1.de')
,('http://www.test2.de/login.asp')
,('http://www.test3.fr/login.other')
,('http://www.test4.co.uk/login.asp')
,('http://www.test5.com')
,('http://www.test6.tv')
,('http://www.test7.asp/login.asp');

DECLARE @filter VARCHAR(MAX)=
STUFF
(
    (
        SELECT 'OR URL LIKE ''%' + TLD + '/%.asp'' '
        FROM #TLDs
        FOR XML PATH('')
    ),1,3,''
)

DECLARE @cmd VARCHAR(MAX)=
'SELECT * FROM #URLs AS u WHERE ' + @filter;

EXEC (@cmd);

DROP TABLE #URLs;
DROP TABLE #TLDs;

The result

http://www.test2.de/login.asp
http://www.test4.co.uk/login.asp

Upvotes: 1

Related Questions