String Function

Create FUNCTION [dbo].[UF_CSVToTable]
(
@psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
DECLARE @sTemp VARCHAR(10)
WHILE LEN(@psCSString) > 0
BEGIN
SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
LEN(@psCSString)))
SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
LEN(@psCSString)) + 1, LEN(@psCSString))
INSERT INTO @otTemp VALUES (@sTemp)
END
RETURN
END


Input : Select * from UF_CSVToTable('1,2,3')

Output:: SID
             1
             2
             3

Split Example

DECLARE @xml xml,@str varchar(100),@delimiter varchar(10)
SET @str= 'AAA 65785 89'
SET @delimiter =' '
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
select @xml
SELECT a.value('.','varchar(10)') as value FROM @xml.nodes('X') as X(a)