Combine LIKE & IN? - SQL Server Forums

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

Transact-SQL (2000)

Combine LIKE & IN?

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2004-01-23 : 10:26:40

Is there a way to do this?

SELECT Field1 WHERE Field2 LIKE IN (@myList)

I want to perfrom a LIKE criteria selection within a list.

Is it faster to use IN() as opposed to (Field1 = This or Field1 = That, etc)?

stephe40
Posting Yak Master

218 Posts

Posted - 2004-01-23 : 10:44:38

It really doesnt matter. SQL Server's query optimizer will choose what it thinks will be faster. It may substitute WHERE field1 IN (values) with field1=this OR field1=that. Check the execution plan to see what its really doing.

In your case you cant use a variable in the IN clause. (If your passing in a comma delimited string.) You going to want to pass in an xml string of the values in @myList and use openxml as the source of the IN clause.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-23 : 14:26:00

Read up on creating a table variable from a CSV variable. that would be step 1.

Then you can say:

SELECT A.Field1
FROM YourTable A
INNER JOIN @CSV_Table_Variable B
ON A.Field1 LIKE B.Value
GROUP BY A.Field1

you must GROUP BY all columns you wish to include in your select list. in fact, make sure you have the PK of the table included in your GROUP BY expression or you may return less rows than you intended to. There are other ways to write this, but this might be the easiest to understand.

check the articles of this site for info on putting the contents of a CSV string variable into a table, temp table, or table variable.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources