stored procedure csv input

Question

Possible Duplicate:
Split Function equivalent in tsql?
SQL Search column for each variable in CSV string

I have a stored procedure which takes input @Notifications as 34181,34182,34184 etc, a string.

Now, I want to split this string in such a way that another variable @Notification will contain first time 34181 and next time 34182 etc.

These numbers 34181 length is not fixed. It can be 341812 or just 3 also because of this I cannot use substr function.

How can I split this string?

Top right, search: http://stackoverflow.com/search?q=%5Bsql-server%5D+%2Bsplit. You have have never voted and never accepted any answers. — gbn, Nov 24 '11 at 07:00
Check out the following thread: http://stackoverflow.com/questions/2647/split-string-in-sql — Aaron, Nov 24 '11 at 07:04

score 0 · Answer 1 · answered Nov 24 '11 at 07:02

0

you can use function charIndex with combination of substring function. e.g.

select Phone,charindex('-',Phone) from customers

Result

030-0074321    4

http://dotnetguts.blogspot.com/2008/05/instr-function-in-sql-server.html

answered Nov 24 '11 at 07:02

Zohaib

7,026
3
26
35

You can't for repeated values... – gbn Nov 24 '11 at 07:03
My approach would be get index of first comma, and get value from zero index to comma index, that will be my first value. Now take substring from original String such that Originial String = (commaIndex + 1, Originial String Length)....Now again finad index of comma.... – Zohaib Nov 24 '11 at 07:06
@gbn I guess it should work, I dont know of some better String function to achieve this goal, although i will search – Zohaib Nov 24 '11 at 07:07
I am trying to Contains function to search comma but it throws an error incorrect syntax – user998533 Nov 24 '11 at 08:27
I have used charindex and its working thanks. – user998533 Nov 24 '11 at 12:20

score 0 · Answer 2 · answered Nov 24 '11 at 08:39

While you already have many links on how to create a string.Split function in SQL, a simple way for this is:

SELECT * 
FROM Customers
WHERE ',' + @CustomerID + ',' LIKE '%,' + Customers.CustomerID + ',%'

While it will not use any index on Customer ID, if you only have a small table this is fine, otherwise duplicate the string.split method in a function and call that.

SELECT * 
FROM Customers
WHERE CustomerID IN (SELECT Value FROM dbo.Split(@CustomerIDs, ','))

Or very similar to that.

stored procedure csv input

2 Answers2