SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to check IN clause for strings
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mng
Yak Posting Veteran

59 Posts

Posted - 01/14/2013 :  07:13:21  Show Profile  Reply with Quote
I have a requirement to check whether passed names exist in table or not ? How to acieve this? Can some one share the query plz

DECLARE @EMPNAMES = 'John, Deo, Dennis, Haq'

Ex: Select * from table where Empnames IN(@EMPNames)

IN clause will work only for integers. The same how can i acheive with strings.

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/14/2013 :  07:31:44  Show Profile  Reply with Quote
Couple of different ways:

Change the query to:
SELECT * FROM Tbl WHERE
Empnames IN ('John','Deo','Dennis','Haq')


If you do want to use a variable rather than string literals, then

SELECT * FROM Tbl WHERE ','+@EMPNames+',' LIKE '%,'+EmpNames+',%'


While that is quick and easy coding, couple of things to keep in mind:
1. If you have spaces in addition to commas separating the tokens (as you have in the example you posted, you would need to make changes to accommodate that).

2. This would not be able to use any index you may have on EmpNames column.

Another alternative is to split the @EmpNames token into a virtual table and then join with that table. For anything but small tables, I would recommend this second method. If you search SQLTeam or the web, you will find several string splitter functions http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
Go to Top of Page

Mng
Yak Posting Veteran

59 Posts

Posted - 01/14/2013 :  07:39:40  Show Profile  Reply with Quote
James,
Thanks for your reply. I will get the input to stored procedure as 'John,Deo,Dennis,Haq'. My query should return the data which contains these names.
I didn't understand the split funcitionality here. And also your 1st option says pass data as 'John','Deo','Dennis'. How can i pass like this if i get the input as 'John,Deo,Dennis,Haq'.
Appreciate it if you can provide the full solution(query).

quote:
Originally posted by James K

Couple of different ways:

Change the query to:
SELECT * FROM Tbl WHERE
Empnames IN ('John','Deo','Dennis','Haq')


If you do want to use a variable rather than string literals, then

SELECT * FROM Tbl WHERE ','+@EMPNames+',' LIKE '%,'+EmpNames+',%'


While that is quick and easy coding, couple of things to keep in mind:
1. If you have spaces in addition to commas separating the tokens (as you have in the example you posted, you would need to make changes to accommodate that).

2. This would not be able to use any index you may have on EmpNames column.

Another alternative is to split the @EmpNames token into a virtual table and then join with that table. For anything but small tables, I would recommend this second method. If you search SQLTeam or the web, you will find several string splitter functions http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/14/2013 :  08:13:59  Show Profile  Reply with Quote
When the parameter is passed into a stored procedure, you cannot use the first method that I described.

You can use the second method exactly as I had posted earlier.

For the third case, you need to find a splitter function. The example below uses the function that Seventhnight posted as the first reply in the thread referred to in my previous post. Copy his code into an SSMS window and run it to install the function. Then you would use that like shown below:
SELECT
   e.*
FROM
   Tbl e
   INNER JOIN dbo.Split(@EMPNames,',') s
      ON s.Data = e.EmpNames
The function SeventhNight posted uses a while loop to do the splitting. I recall seeing more efficient functions - but don't recall where at the moment; you should be able to find them if you google
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/14/2013 :  08:39:43  Show Profile  Reply with Quote
This is another method

http://visakhm.blogspot.in/2013/01/delimited-string-split-xml-parsing.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000