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
  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))
  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))
  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))
  29. DECLARE @VAL3 VARCHAR(10)
  30. SET @VAL3=@SUBSTR
  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))
  39. SELECT @VAL2=SUBSTRING(@STR,1,CHARINDEX(@DELIM2,@STR)-1)
  40. SELECT @STR=SUBSTRING(@STR,CHARINDEX(@DELIM2,@STR)+1,LEN(@STR))
  42. SET @VAL3=@STR
  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