Wednesday, October 13, 2010

How to split string in SQL server 2005

Split function in SQL Server 2005.

Would you like to split a sting value something like ('one,two,three,four') into rows.

The below function will help out...


CREATE FUNCTION dbo.fnSplit (@text NVARCHAR(4000), @delimiter CHAR)
RETURNS @Result TABLE (Data NVARCHAR(4000))
AS
BEGIN
DECLARE @textXML XML
SELECT @textXML = CAST('' + REPLACE(@text , @delimiter, '') + '' AS XML);

INSERT INTO @Result (Data)
SELECT T.split.value('.', 'nvarchar(max)') AS data FROM @textXML.nodes('/d') T (split)

-- removes the extra lines
delete from @Result where len(data)=0

RETURN
END

--Sample SELECT data from dbo.fnSplit('India,Australia,UK,US,', ',')

No comments:

Post a Comment