Split the values from ('1,2,3,4') to ('1','2','3','4') or (1,2,3,4)

Question

Post reply

Split the values from ('1,2,3,4') to ('1','2','3','4') or (1,2,3,4)

panneermca35

Mr or Mrs. 500

Points: 504
More actions
December 12, 2011 at 3:01 am

#247509

Hi,
I need to pass comma separated values in my procedure but i have problem.
Example:
Declare @STR nvarchar = '1,2,3,4'
select * from emp where empid in(@str)
when i execute this query i got the following error..
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '1,2,3,4' to data type int.
so i need to execute the query like
select * from emp where empid in('1','2','3','4')
or
select * from emp where empid in (1,2,3,4)
so how i can split the @STR value from ('1,2,3,4') to ('1','2','3','4') or (1,2,3,4)[/b]
Help me please.
thank you.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

DECLARE @STR NVARCHAR(MAX) = '1,2,3,4'

SET @STR = '''' + REPLACE(@str,',',''',''') + ''''

SELECT *

FROM emp

WHERE empid IN (@str)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

RowanCollum SSC Veteran Points: 224 More actions · Answer 2

In SS2005, the following may be another possibility:

select * from emp where empid in

( select i.value('text()[1]','int')

from (select cast(''+replace(@str,',','')+'' as xml) as xlist) s

cross apply s.xlist.nodes('/i') X(I)

)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 3

RowanCollum (12/12/2011)
In SS2005, the following may be another possibility:
select * from emp where empid in
( select i.value('text()[1]','int')
from (select cast(''+replace(@str,',','')+'' as xml) as xlist) s
cross apply s.xlist.nodes('/i') X(I)
)

If you have to pass a lot of such parameters, you'll find that's a bit slower than other possibilities. Please see the following article.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 4

Cadavre (12/12/2011)
DECLARE @STR NVARCHAR(MAX) = '1,2,3,4' SET @STR = '''' + REPLACE(@str,',',''',''') + '''' SELECT * FROM emp
WHERE empid IN (@str)

That'll only work if you use dynamic SQL or maybe if you use LIKE with a conversion on empid which would destroy SARGability.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

panneermca35 (12/12/2011)
so how i can split the @STR value from ('1,2,3,4') to ('1','2','3','4') or (1,2,3,4)[/b]
Help me please.
thank you.

Please see the following article.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 6

DECLARE @NUMBER VARCHAR(50)

DECLARE @FINAL VARCHAR(50)

SET @NUMBER = '1,2,3,4,5'

SET @FINAL = '''' + REPLACE (@NUMBER,',', ''',''') + ''''

SELECT @FINAL

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

yubo1 SSChasing Mays Points: 612 More actions · Answer 7

HI You can get the best answer in this topic:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

sami.sqldba (12/13/2011)
DECLARE @NUMBER VARCHAR(50)
DECLARE @FINAL VARCHAR(50)
SET @NUMBER = '1,2,3,4,5'
SET @FINAL = '''' + REPLACE (@NUMBER,',', ''',''') + ''''
SELECT @FINAL

How might this work?

DECLARE @NUMBER VARCHAR(50)

DECLARE @FINAL VARCHAR(50)

SET @NUMBER = '1,2,3,4,5'

SET @FINAL = '''' + REPLACE (@NUMBER,',', ''',''') + ''''

SELECT @FINAL

SELECT *

FROM (SELECT n = '1' UNION ALL SELECT '2' UNION ALL SELECT '3') d

WHERE d.n IN (@FINAL)

-- (0 row(s) affected)

SELECT *

FROM (SELECT n = '1' UNION ALL SELECT '2' UNION ALL SELECT '3') d

WHERE d.n IN (1,2)

-- (2 row(s) affected)

You can force it to work with LIKE but it's not straightforward:

DECLARE @NUMBER VARCHAR(50)

DECLARE @FINAL VARCHAR(50)

SET @NUMBER = '2,4,5,13'

SET @FINAL = '''' + REPLACE (@NUMBER,',', ''',''') + ''''

SELECT @FINAL

SELECT *

FROM (SELECT n = '1' UNION ALL SELECT '2' UNION ALL SELECT '3') d

WHERE @FINAL LIKE '%'+d.n+'%'

-- (3 row(s) affected)

SELECT *

FROM (SELECT n = '1' UNION ALL SELECT '2' UNION ALL SELECT '3') d

WHERE ' ,'+@FINAL+', ' LIKE '%,'''+d.n+''',%'

-- (1 row(s) affected)

Best to follow Jeff's advice and split the values out.

Edit: changed some values in second declaration of @Final.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Narud SSCrazy Points: 2837 More actions · Answer 9

You can create a function like this that returns the values in a table:

CREATE FUNCTION dbo.fString2Table (

@String as nvarchar(MAX),

@Delimiter as nvarchar(1)

)

RETURNS @ReturnTable TABLE (Value nvarchar(200))

AS

BEGIN

DECLARE @iLen AS int,

@iStart AS int,

@iEnd AS int,

@Value AS nvarchar(200)

SELECT @iLen = LEN(@String), @iStart = 1

WHILE CHARINDEX(@Delimiter, @String, @iStart) > 0

BEGIN

SET @iEnd = CHARINDEX(@Delimiter, @String, @iStart)

SET @Value = SUBSTRING(@String, @iStart, @iEnd - @iStart)

SET @iStart = @iEnd + 1

INSERT INTO @ReturnTable (Value) VALUES (@Value)

END

SET @iEnd = CHARINDEX(@Delimiter, @String, @iEnd)

SET @Value = SUBSTRING(@String, @iStart, @iLen - @iEnd)

INSERT INTO @ReturnTable (Value) VALUES (@Value)

RETURN

END

And use it in this ways

SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')

Or

SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')

WHERE Value = CONVERT(NVARCHAR(200), @intVar)

Or even this

IF EXISTS (SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')

WHERE Value = CONVERT(NVARCHAR(200), @intVar))

SELECT 'TRUE'

ELSE

SELECT 'FALSE'

I hope this would be helpful for you.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

Narud (12/14/2011)
You can create a function like this that returns the values in a table:
<<snip>>
And use it in this ways
SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')
Or
SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')
WHERE Value = CONVERT(NVARCHAR(200), @intVar)
Or even this
IF EXISTS (SELECT Value FROM dbo.fString2Table('1,2,3,4,5', ',')
WHERE Value = CONVERT(NVARCHAR(200), @intVar))
SELECT 'TRUE'
ELSE
SELECT 'FALSE'
I hope this would be helpful for you.

You could, but the one referred to in Jeff Moden's post above is tried and tested - and much much faster.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]