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