Post
Ask Question

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:

output

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.

  1. CREATE PROCEDURE [DBO].[GOVIND_TOKENIZESTRINGTOTABLE]    
  2. (    
  3.     @STR VARCHAR(500),@DELIM1 VARCHAR(10),@DELIM2 VARCHAR(10)    
  4. )    
  5. AS    
  6. BEGIN    
  7.     IF OBJECT_ID('TempDb..#TEMP') IS NOT NULL    
  8.     DROP TABLE #TEMP    
  9.         
  10.     CREATE TABLE #TEMP( NAME VARCHAR(50),    
  11.                         ADDRESS VARCHAR(50),    
  12.                         STATION INT    
  13.                       )     
  14.     DECLARE @SUBSTR VARCHAR(50)       
  15.     SET @SUBSTR=''      
  16.     WHILE(CHARINDEX(@DELIM1,@STR)!=0)    
  17.     BEGIN    
  18.         SELECT @SUBSTR=SUBSTRING(@STR,1,CHARINDEX(@DELIM1,@STR)-1)    
  19.         SELECT @STR=SUBSTRING(@STR,CHARINDEX(@DELIM1,@STR)+1,LEN(@STR))    
  20.                     
  21.         DECLARE @VAL1 VARCHAR(50)    
  22.         SELECT @VAL1=SUBSTRING(@SUBSTR,1,CHARINDEX(@DELIM2,@SUBSTR)-1)    
  23.         SELECT @SUBSTR=SUBSTRING(@SUBSTR,CHARINDEX(@DELIM2,@SUBSTR)+1,LEN(@SUBSTR))    
  24.     
  25.         DECLARE @VAL2 VARCHAR(50)    
  26.         SELECT @VAL2=SUBSTRING(@SUBSTR,1,CHARINDEX(@DELIM2,@SUBSTR)-1)    
  27.         SELECT @SUBSTR=SUBSTRING(@SUBSTR,CHARINDEX(@DELIM2,@SUBSTR)+1,LEN(@SUBSTR))    
  28.             
  29.         DECLARE @VAL3 VARCHAR(10)    
  30.         SET @VAL3=@SUBSTR    
  31.             
  32.         INSERT INTO #TEMP VALUES(@VAL1,@VAL2,@VAL3)    
  33.     END    
  34.     IF @STR IS NOT NULL    
  35.     BEGIN    
  36.         SELECT @VAL1=SUBSTRING(@STR,1,CHARINDEX(@DELIM2,@STR)-1)    
  37.         SELECT @STR=SUBSTRING(@STR,CHARINDEX(@DELIM2,@STR)+1,LEN(@STR))    
  38.     
  39.         SELECT @VAL2=SUBSTRING(@STR,1,CHARINDEX(@DELIM2,@STR)-1)    
  40.         SELECT @STR=SUBSTRING(@STR,CHARINDEX(@DELIM2,@STR)+1,LEN(@STR))    
  41.             
  42.         SET @VAL3=@STR    
  43.             
  44.         INSERT INTO #TEMP VALUES(@VAL1,@VAL2,@VAL3)    
  45.     END    
  46.     SELECT * FROM #TEMP    
  47. END  
Execution of Stored Procedure:
  1. EXEC DBO.GOVIND_TOKENIZESTRINGTOTABLE 'UTTAM ,HYD,1|GOVIND,TNL,2|KIRAN,GNT,3','|',','  
Result:

result