Split String of Records and insert into Table in SQL Server

The string contains set of records and each record is separated by Pipe delimiter and column values are separated by comma delimiter. For example, we have a string such as 'UTTAM,HYD,1|GOVIND,TNL,2|KIRAN,GNT,3'. After inserting the records into the table, the output will be like the following:



I have written a stored procedure which will take 3 inputs, one is string of records, 2nd one is Record delimiter, and 3rd one is column value delimiter. Here we have choice to pass different delimiters for record separation and column value separation.

CREATE PROCEDURE [DBO].[GOVIND_TOKENIZESTRINGTOTABLE]
(
    @STR VARCHAR(500),@DELIM1 VARCHAR(10),@DELIM2 VARCHAR(10)
)
AS
BEGIN
    IF OBJECT_ID('TempDb..#TEMP') IS NOT NULL
    DROP TABLE #TEMP

    CREATE TABLE #TEMP( NAME VARCHAR(50),
                        ADDRESS VARCHAR(50),
                        STATION INT
                      )
    DECLARE @SUBSTR VARCHAR(50)
    SET @SUBSTR=''
    WHILE(CHARINDEX(@DELIM1,@STR)!=0)
    BEGIN
        SELECT @SUBSTR=SUBSTRING(@STR,1,CHARINDEX(@DELIM1,@STR)-1)
        SELECT @STR=SUBSTRING(@STR,CHARINDEX(@DELIM1,@STR)+1,LEN(@STR))

        DECLARE @VAL1 VARCHAR(50)
        SELECT @VAL1=SUBSTRING(@SUBSTR,1,CHARINDEX(@DELIM2,@SUBSTR)-1)
        SELECT @SUBSTR=SUBSTRING(@SUBSTR,CHARINDEX(@DELIM2,@SUBSTR)+1,LEN(@SUBSTR))

        DECLARE @VAL2 VARCHAR(50)
        SELECT @VAL2=SUBSTRING(@SUBSTR,1,CHARINDEX(@DELIM2,@SUBSTR)-1)
        SELECT @SUBSTR=SUBSTRING(@SUBSTR,CHARINDEX(@DELIM2,@SUBSTR)+1,LEN(@SUBSTR))

        DECLARE @VAL3 VARCHAR(10)
        SET @VAL3=@SUBSTR

        INSERT INTO #TEMP VALUES(@VAL1,@VAL2,@VAL3)
    END
    IF @STR IS NOT NULL
    BEGIN
        SELECT @VAL1=SUBSTRING(@STR,1,CHARINDEX(@DELIM2,@STR)-1)
        SELECT @STR=SUBSTRING(@STR,CHARINDEX(@DELIM2,@STR)+1,LEN(@STR))

        SELECT @VAL2=SUBSTRING(@STR,1,CHARINDEX(@DELIM2,@STR)-1)
        SELECT @STR=SUBSTRING(@STR,CHARINDEX(@DELIM2,@STR)+1,LEN(@STR))

        SET @VAL3=@STR

        INSERT INTO #TEMP VALUES(@VAL1,@VAL2,@VAL3)
    END
    SELECT * FROM #TEMP
END

Execution of Stored Procedure:

EXEC DBO.GOVIND_TOKENIZESTRINGTOTABLE 'UTTAM ,HYD,1|GOVIND,TNL,2|KIRAN,GNT,3','|',','

Result: