Oct 29, 2015

Convert row and column delimited string to table in SQL Server

We can change row and field delimited string in tabular format. We are going to create a stored procedure which accept delimited data, row and column delimiter (comma, pipe, new line, colon etc) as a parameter and will return a result set:

CRETAE PROCEDURE DelimitedStringToTable (
      @Data AS VARCHAR(MAX)
      ,@RowDelimiter AS VARCHAR(5)
      ,@FieldDelimiter AS VARCHAR(5)
      )
AS
BEGIN
      DECLARE @DyQuery AS VARCHAR(MAX)

      SET @DyQuery = 'SELECT ''' + REPLACE(REPLACE(@data, @RowDelimiter, ''' UNION ALL SELECT '''), @FieldDelimiter, ''',''')

      IF RIGHT(@DyQuery, 19) = ' UNION ALL SELECT '''
            SET @DyQuery = LEFT(@DyQuery, LEN(@DyQuery) - 19)

      EXEC (@DyQuery)
END

To Execute:

EXECUTE DelimitedStringToTable '1|b|c|d;2|q||e;',';','|'

To Load output into a table:

DECLARE @DataTable TABLE(C1 INT,C2 VARCHAR(10),C3 VARCHAR(10),C4 VARCHAR(10))

INSERT INTO @DataTable
EXECUTE DelimitedStringToTable '1|b|c|d;2|q||e;',';','|'

SELECT * FROM @DataTable

Sample output:

C1
C2
C3
C4
1
b
c
d
2
q

e

No comments:

Post a Comment