Alternative for LIKE with multiple rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Alternative for LIKE with multiple rows

Author

Topic

anundsson
Starting Member

8 Posts

Posted - 2006-05-23 : 07:23:56

Hi,

Here is what i would like to do..

SELECT *
FROM table1
WHERE table1.column1 NOT LIKE (SELECT column2 FROM table2)

But you cant use LIKE if the subquery returns more than 1 value.

Is there any way to solve this?
Any help is much appreciated...

mr_mist
Grunnio

1870 Posts

Posted - 2006-05-23 : 07:36:19

I think you need NOT IN, but it's diffcult to say.

-------
Moo. :)

anundsson
Starting Member

8 Posts

Posted - 2006-05-23 : 07:58:42

Thanks for your reply.

I was not very thorough in my description of the problem.
I need to use wildcards in the strings from column2.

For example, column2 could contain ‘www.google.com%’, ‘www.gmail.com%’ etc.
Any row containing urls from column2 should not be returned.

Any ideas?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-23 : 08:09:38

[code]select *
from table1
where pkey not in
(SELECT pkey
FROM table1
inner join table2 on table1.column1 LIKE table2.column2)
[/code]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-23 : 08:12:44

How about somthing like this



Declare @tblTmp Table 
(
col1 varchar(100)
)

Declare @tblTmp1 Table 
(
col1 varchar(100)
)

Insert @tblTmp 
Select 'ABC' Union All 
Select 'BCD' Union All 
Select 'XYZ' 

Insert @tblTmp1
Select 'A' Union All
Select 'Z'

Select Col1 From @tblTmp  
Where Col1 Not in (Select a.Col1 from @	tblTmp a Inner Join @tblTmp1 b On a.Col1 Like '%' + b.Col1 + '%')

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

anundsson
Starting Member

8 Posts

Posted - 2006-05-23 : 09:32:46

Both solutions work great.

Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources