Tuesday, September 24, 2013

SQL Server: User Defined Function to Split delimited text

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