CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
urlreader
urlreader

Reputation: 6615

Match any patterns in a table?

I know we can use LIKE for pattern matching, however, here is what want to do.

I have a table, which has a column, 'Pattern', the values are like:

host1%
%host2
....

I have another table, which has a column, 'Host'. The question is: how can I check whether the values in 'Host' table do not match any patterns in 'Pattern'?

If it is too complex, then a simplified question is: How can I check whether the values in 'Host' do not StartWith any strings in 'Pattern'?

We can use loop, but is there a better way? ideally, it should work for ql server 2008, but latest version will do.

thanks

Upvotes: 0

Views: 331

Answers (2)

JohnLBevan
JohnLBevan

Reputation: 24470

Use where not exists followed by a subquery which checks each pattern against the current row of the table containing your data. i.e.

where not exists 
(
    select top 1 1
    from @patterns p
    where d.datum like p.pattern
)

Full Code for Working Example: SQL Fiddle

declare @patterns table 
(
    pattern nvarchar(16) not null
)
declare @data table
(
    datum nvarchar(16) not null
)
insert @patterns
values ('host1%')
,('%host2')
insert @data
values ('host1234')
, ('234host1')
, ('host2345')
, ('345host2')

select *
from @data d
where not exists 
(
    select top 1 1
    from @patterns p
    where d.datum like p.pattern
)

Upvotes: 2

juergen d
juergen d

Reputation: 204894

select t1.host
from table_1 t1
left join table_2 t2 on t1.host like t2.pattern
where t2.pattern is null

Upvotes: 1

Related Questions