Sunday, November 15, 2009

Split a string into a table

CREATE FUNCTION [fnSplit]
(@sInputList VARCHAR(MAX)
, @sDelimiter VARCHAR(10) = ',')
RETURNS @List TABLE (item NVARCHAR(4000))
BEGIN
DECLARE @sItem NVARCHAR(8000)
WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
BEGIN
SELECT
@sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList, 0) - 1))),
@sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList,0) + LEN(@sDelimiter), LEN(@sInputList))))
  IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList
RETURN
END

No comments:

Post a Comment