Thursday, July 5, 2012

MSSQL Server split function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnSplit] (
    @sInputList VARCHAR(8000)  -- List of delimited items 
    , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    )
    RETURNS @List TABLE (Offset int, item VARCHAR(8000))
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    declare @index int
    set @index = 0
    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 @index,  @sItem
            set @index = @index + 1
    END
   
    IF LEN(@sInputList) > 0
        INSERT INTO @List SELECT @index, @sInputList
            set @index = @index + 1 -- Put the last item in
    RETURN
    END

This Function returns a Table with two fields.
1) Offset a numeric value you can also use that a Sequence no and
2) Item

Thank you.