Can you use Like with the IN() function?

[n2jesus]

Hi all,
Here's what I need to do...

I need to find things like '%%12345%%' IN(field1, field2, field3)

Is there a way to do this?

This is a simple example. I need to look for a substring that may occur in up to 20 fields. If it's there I want to return the row.

Thanks for any input,

jim

 

[SkipVought]

In works like this...

Where Field1 IN ([i]Your list of VALUES[/i])

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[n2jesus]

ahhh, but it also works the other way.

you can go:

select * from table where '1234' in(field1, field2, field3)

It will show you any row where it finds '1234' in one of those 3 fields.

I just want to make it fuzzier by also using like.

 

[SkipVought]

I guess I never thought of using it that way, because I have never encountered non-normalized tables with multiple fields containing similar data items. It certainly does not seem to be a NORMAL occurrence.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[markros]

Check this thread on MSDN:

http://social.msdn.microsoft.com/Fo...l/thread/66606ab9-d6c8-412c-9283-a99a2c4ddf96

though if you're looking for a string inside any of the field in the field list, then I just answered a similar question recently here:

select * from myTable where charindex(myStringToSearch,field1 + ' ' + field2 + ' ' + Field3) > 0

PluralSight Learning Library

 

[gmmastros]

This will be horrible for performance, and may cause some unexpected problems, too.

Declare @Temp Table(Col1 VarChar(20), Col2 VarChar(20), Col3 VarChar(20))

Insert Into @Temp Values('Data1','Data2','Data3')
Insert Into @Temp Values('','','')
Insert Into @Temp Values('This is Data','Boring Stuff',NULL)
Insert Into @Temp Values('Apple','No Data Here','Grape')
Insert Into @Temp Values('','','Last Data Column')


Select * From @Temp
Where  Coalesce(Col1,'') + Coalesce(Col2,'') + Coalesce(Col3, '') Like '%Data%'

Coalesce is needed to accommodate the concatenation of nulls. All columns are concatenated and then a like comparison is performed.

There are a couple problems I can think of right off the top of my head. Since the columns are concatenated and then checked, you may get erroneous results. For example:

Declare @Temp Table(Col1 VarChar(20), Col2 VarChar(20), Col3 VarChar(20))

Insert Into @Temp Values('Data1','Data2','Data3')
Insert Into @Temp Values('','','')
Insert Into @Temp Values('This is Data','Boring Stuff',NULL)
Insert Into @Temp Values('Apple','No Data Here','Grape')
Insert Into @Temp Values('','','Last Data Column')


Select * From @Temp
Where  Coalesce(Col1,'') + Coalesce(Col2,'') + Coalesce(Col3, '') Like '%[!]LENO[/!]%'

Weird, huh? Well APPLE ends with LE and "NO DATA HERE" starts with NO. LENO.

Also, if you are comparing data from columns in different tables, it is possible that the resulting concatenation of many columns could exceed varchar(8000) and you would end up with missing certain results.

I won't even talk about performance. There is no possible way to get this query to do an index seek. You'll constantly get index scans. With large tables (many rows), the performance will degrade very rapidly.


But.... it is kinda what you wanted. Sure... it doesn't use IN, but the results are the same.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

This is the thread thread183-1598780 also add coalesce(Field1,'') + space(1) +coalesce(field2,'') + space(1)

Note, that this search may be slow as it will not use any indexes.

PluralSight Learning Library

 

[markros]

George,

Great minds thing alike Just add a separators character between your fields such as a space or | if string can contain spaces.

PluralSight Learning Library

 

[markros]

think, not thing, of course

PluralSight Learning Library

 

[gmmastros]

I was just thinking about the separator. Space is not exactly the best separator to use. Instead, I would suggest Char(7), which is the ASCII bell character. It is less likely that a user will search for that. Including a space in a search kinda happens a lot.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom