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