asked
Parsing Data from single column to Multiple columns
Hi, I want to split my data ("|" delimited) from one column to multiple columns. i.e. Source column Data : Col1 "ABCD|EFGH|IJKL|MNOP" Desired Resultset : Col1 Col2 col3 col4 "ABCD" "EFGH" "IJKL" "MNOP" Please suggest me way to do it. Thanks, Pratik
you can use the DTS Wizard and then column delimeter as Vertical Bar {|}. or if it is any column of a table already make a new copy of it, it may solve the issue here.!
I personally use below function developed by Mr. Jeff Moden CREATE FUNCTION dbo.DelimitedSplit8K --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT t.N+1 FROM cteTally t WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) ) --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1), Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)) FROM cteStart s ; For more help you can follow this link
http://www.sqlservercentral.com/articles/Tally+Table/72993/ Cheers.
I too was thinking about that. But how would you go about if you have a table with one column - S - which contains pipe-delimited strings, and you want a table as result, with columns Col1, Col2, Col3 and Col4? I itched my head for a sec and then realized I don't have time to spend on it right now :)
hmmm... nice thought. But don't you think the PIVOT/UNPIVOT would do the job ;)
It really depends on the method and version of SQL Server. SSIS can do this for you with relative ease. As stated above, T-SQL can also accomplish this task. Ultimately, it is up to your resources on how you can go about making this happen.
Sorry I had the last day as holiday. No work at all. (Lucky enough to enjoy more holidays in upcoming week :)). Why don't you convert UTF8 encoded file into UTF16 encoded file using -w parameter in bcp utility to specify the encoding is UTF16. One thing I must mention that after using -w option, the default delimiter is changed from | to \t, hence it is necessary to update the delimiter as well. Please let us know if this is useful.
Hi, I am trying to create one generic SSIS package over here for UTF-8 input files and bulk Insert does not support UTF-8 so I am trying to following approch. 1) Load data in single column (staging table) using Data Flow Task (as it supports UTF-8). 2) Create script to get data from single column to multiple columns. 3) Load into destination table. Any help is very much appriciated.
