SQL developers frequently need to split comma delimited text or string delimited by any other delimiter. I am placing a generic user defined function to split the delimited text and provide the result in form of table. This can be used in various ways to achieve similar scenarios.
CREATE FUNCTION [dbo].[UDF_Split_DelimitedString](
@DelimitedString nVARCHAR(MAX) -- delimited string
, @Delimiter VARCHAR(20) -- delimiter
) RETURNS @SplitedList TABLE (SplitedText nVARCHAR(MAX))
BEGIN
DECLARE @String nVARCHAR(MAX)
WHILE CHARINDEX(@Delimiter,@DelimitedString,0) <> 0
BEGIN
SELECT
@String=RTRIM(LTRIM(SUBSTRING(@DelimitedString,1,CHARINDEX(@Delimiter,@DelimitedString,0)-1))),
@DelimitedString=RTRIM(LTRIM(SUBSTRING(@DelimitedString,CHARINDEX(@Delimiter,@DelimitedString,0)+LEN(@Delimiter),LEN(@DelimitedString))))
IF LEN(@String) > 0
INSERT INTO @SplitedList SELECT
@String
END
IF LEN(@DelimitedString) > 0
INSERT INTO @SplitedList SELECT
@DelimitedString
RETURN
END
GO
Test script.
SELECT * FROM [dbo].[UDF_Split_DelimitedString]('1,2,3,4,5', ',')
No comments:
Post a Comment