Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
I need to find out single userid within a comma separated list of userid's but the problem is that userid is integer and the list of userid's is in nvarchar as it contains comma separated list so therefore it is showing the following error
 
Conversion failed when converting the nvarchar value '11,18' to data type int.
 
Please let me know how can I do it.
thnx in advance.
Posted 26-Apr-13 2:41am
Comments
ThePhantomUpvoter at 26-Apr-13 7:46am
   
That is because the string "11,18" cannot be converted to an int. You need use the string manipulation functions to split the string.
jackson kumar at 26-Apr-13 8:19am
   
but how i can do it??
ThePhantomUpvoter at 26-Apr-13 8:37am
   
You have to actually try something. This has been asked and answered many many many times already.
jackson kumar at 26-Apr-13 8:42am
   
the values in the userid list is in the form ('1,15,16,33') and this is showing error but if i'm using (1,15,16,33) then it is working fine and list of userid'd is populated with the output of an sql query so how can i get into (1,15,16,33) form.
gvprabu at 26-Apr-13 8:14am
   
Split function error - The statement terminated. The maximum recursion 100 has been exhausted before statement completion[^]
 
Check the post... U can you UDF (User Define Functions) for Split Process then Convert it will works.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can achieve that using CTE[^].
DECLARE @tbl TABLE(Ids VARCHAR(50))
INSERT INTO @tbl (Ids)
VALUES('1,2,3,5')
INSERT INTO @tbl (Ids)
VALUES('7,11,12,15,33')
INSERT INTO @tbl (Ids)
VALUES('9,10,22,35,43')
INSERT INTO @tbl (Ids)
VALUES('13,19,32,45,53')
 

;WITH cteIDs AS
(
	SELECT ROW_NUMBER() OVER(ORDER BY Ids) AS RowNo, CONVERT(INT, LEFT(Ids, CHARINDEX(',',Ids)-1)) AS Id, RIGHT(Ids,LEN(Ids)-CHARINDEX(',',Ids)) AS Remainder
	FROM @tbl
	WHERE CHARINDEX(',',Ids)>0
	UNION ALL
	SELECT RowNo, CONVERT(INT, LEFT(Remainder, CHARINDEX(',',Remainder)-1)) AS Id, RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder)) AS Remainder
	FROM cteIds
	WHERE CHARINDEX(',',Remainder)>0
	UNION ALL
	SELECT RowNo, Remainder AS Id, NULL AS Remainder
	FROM cteIds
	WHERE CHARINDEX(',',Remainder)=0
)
SELECT *
FROM cteIds
ORDER BY Id, RowNo
 
Result:
RowNo   Id      Remainder
1	1	2,3,5
1	2	3,5
1	3	5
1	5	NULL
3	7	11,12,15,33
4	9	10,22,35,43
4	10	22,35,43
3	11	12,15,33
3	12	15,33
2	13	19,32,45,53
3	15	33
2	19	32,45,53
4	22	35,43
2	32	45,53
3	33	NULL
4	35	43
4	43	NULL
2	45	53
2	53	NULL
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
U can you UDF (User Define Functions) for Split Process then Convert it will works.
Check the post...
Split function error - The statement terminated. The maximum recursion 100 has been exhausted before statement completion[^]
Regards,
GVPrabu
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 26 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100