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