I use functions extensively when they first came out.
It seems like in SQL 2000, there were plenty of limits to what you could and couldn’t do with User-Defined-Functions.
Now, with the release of SQL 2005 / 2008 and the CROSS APPLY clause, it is extremely powerful to parse text using Split functionality.
Usage:
Select * From dbo.fnSplit(’2,3,4,5,6,7,8′, ‘,’)
This returns a table with two columns
item – first row would have 2, second row would have 3, etc
itemCount – first row would have 1, second row would have 2, etc. This is similar to the RANK function, it is really nice sometimes to have this built into the result sets
CREATE FUNCTION [dbo].[fnSplit]
(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
)
RETURNS @List TABLE (item VARCHAR(8000), itemCount int identity(1,1))
BEGIN
DECLARE @sItem VARCHAR(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 -- Put the last item in
RETURN
END
