none
How to split string in SQL server 2005

    General discussion

  • 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('<d>' + REPLACE(@text , @delimiter, '</d><d>') + '</d>' 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,', ',')

    Saturday, February 27, 2010 12:59 AM

All replies