Splitting the string

Functionality :: Split String value

Make on function which can split string value.

Pass string name and delimeter in to this function and get result in Table format.

Using following function you can easily split value

 

Logic

Function

CREATE FUNCTION dbo.Split

(

    @RowData nvarchar(2000),

    @SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

    Id int identity(1,1),

    Data nvarchar(100)

)

AS

BEGIN

    Declare @Cnt int

    Set @Cnt = 1

 

    While (Charindex(@SplitOn,@RowData)>0)

    Begin

        Insert Into @RtnValue (data)

        Select

            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

 

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

        Set @Cnt = @Cnt + 1

    End

  

    Insert Into @RtnValue (data)

    Select Data = ltrim(rtrim(@RowData))

 

    Return

END

Execute Function and Insert into Table

 

DECLARE @str as varchar(max)='abc,123,xyz@abc,123,xyz@abc,123,xyz'

 

INSERT INTO TableName (Data)
SELECT Data
FROM dbo.Split(@str,'@')