T-SQL Script to Split a column with delimited string into multiple columns

This article is about multiple ways of splitting a column with delimited string into multiple columns .If the number of delimiters are 3 or less than that then PARSENAME function can be used to Split a column with delimited string into multiple columns like shown below : To make

 
 
 
 
 
3.9 Star
(17)
5,307 times
Add to favorites
Databases
8/3/2013
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Split using recursion and pivot
    1 Posts | Last post June 08, 2017
    • declare @log table (PK int, Info nvarchar(512));
      insert into @log
      values (1,N'10|ABCD|Row 10|There was a problem|2017-05-03 00:03:20|623909|Light') 
      	, (2,N'20|EFGH|Row 20|All good|2017-05-03 00:03:30|623909|Light') 
      	, (3,N'30|IJK|Row 30|No problems here|2017-05-03 00:03:40|623909|Light') 
      	, (4,N'40|LMN|Row 40|Nothing to see here|2017-05-03 00:03:50|623909|Light') 
      	, (5,N'50|OPQR|Row 50|No problem|2017-05-03 00:04:00|623909|Light') 
      ;
      -- use recursion to split the Info into rows
      ;with tmp( PK, Id, Col, DataItem, Info) 
      as ( select PK, 1 
      		, cast('Record' as varchar(20))	-- need the cast as the length may increase for subsequent rows
      		, cast(substring(Info, 1, CHARINDEX(N'|',Info+N'|')-1) as nvarchar(512)) -- need the cast as the length may increase for subsequent rows
      		, STUFF(Info, 1, CHARINDEX(N'|',Info+N'|'), N'')
      	from @log
      	union all
      	select PK, Id+1
      		, cast(case ID when 1 then 'Short ref.' when 2 then 'Message' when 3 then 'Other Info.' when 4 then 'Tme Stamp' when 5 then 'Available Mem.' when 6 then 'Memory Load' else 'What!' end  as varchar(20))
      		, cast(substring(Info, 1,CHARINDEX(N'|',Info+N'|')-1) as nvarchar(512))
      		, STUFF(Info, 1, CHARINDEX(N'|',Info+N'|'), N'')
      	from tmp
      	where Info > N''
      )	
      --Pivot the rows back to a single rows for each PK
      select PK,[Record],[Short ref.],[Message],[Other Info.],[Tme Stamp],[Available Mem.],[Memory Load]
      from (select PK, Col, DataItem from tmp) as d
      PIVOT (max(d.DataItem) for d.Col in ([Record],[Short ref.],[Message],[Other Info.],[Tme Stamp],[Available Mem.],[Memory Load])) as p
      ;
      
  • ...But
    1 Posts | Last post December 18, 2014
    • It doesn't work :
      - if the string contains non-friendly XML characters.
      - if we have more than one semicolon 
      
      The parsename is used fro SQL Server Object not to manipulate string 
      
      this not elegant solution !!
      http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html
      
      
  • never knew about PARSENAME
    1 Posts | Last post November 23, 2014
    • very elegant solution, I was going crazy with the "charindex-substring"
  • Great Script~
    1 Posts | Last post October 09, 2014
  • great approach..but
    1 Posts | Last post April 02, 2014
    • It doesn't work if the string contains non-friendly XML characters.
  • Good One
    1 Posts | Last post January 29, 2014
  • One more option
    1 Posts | Last post November 06, 2013
    • Sathya, 
      This is great! Please find one more option as in the below link:
      
      http://gallery.technet.microsoft.com/T-SQL-Script-Splitting-a-33ae721c