Come splittare una stringa in SQL Server

Ecco una semplice funzione in Sql Server per splittare una stringa troppo lunga con pochissime righe di codice. Spero possa esserti di aiuto.

CREATE FUNCTION dbo.fnSplit(

@sInputList VARCHAR(8000) — List of delimited items
, @sDelimiter VARCHAR(8000) = ‘,’ — delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

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
GO
–Test
select * from fnSplit(‘1,22,333,444,,5555,666’, ‘,’)
select * from fnSplit(‘1##22#333##444′,’##’) –note second item has embedded #
select * from fnSplit(‘1 22 333 444 5555 666’, ‘ ‘)