December 14, 2005 at 4:47 am
How can I split the string in SQL
For example I am recieving a parameter in stored procedure like this
'RN,BN'
I want to split that in string like that
'RN','BN'
December 14, 2005 at 6:27 am
search for "Split function" on this site and you will get quite a few scripts.
Here is one of them
http://www.sqlservercentral.com/scripts/contributions/1529.asp
December 16, 2005 at 1:19 am
And that function does a lousy job.. Here is an example of a function that does it much more efficiently.
CREATE FUNCTION dbo.fnListToSet (@list VARCHAR(8000), @delimiter VARCHAR(3))
RETURNS TABLE
AS
RETURN
SELECT s.tuple
FROM (
SELECT SUBSTRING(
@delimiter + @list + @delimiter
, numbers.n + LEN(@delimiter)
, CHARINDEX(@delimiter, @delimiter + @list + @delimiter, numbers.n + LEN(@delimiter)) - numbers.n - LEN(@delimiter)
) AS tuple
FROM (
SELECT D0.d*1 + D1.d*10 + D2.d*100 + D3.d*1000
FROM (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D0 (d)
, (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D1 (d)
, (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D2 (d)
, (SELECT 0
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D3 (d)
) AS numbers (n)
WHERE SUBSTRING(
@delimiter + @list + @delimiter
, numbers.n
, LEN(@delimiter)
) = @delimiter
AND n 0
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply