Split String to Separate Columns


  

  create table test (strCol varchar(20))
insert into test values('1-1141-0065-0111') 

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,mycte as (
select strCol, n, substring(Replace(strCol,'-',''), n, 1)  splitVal
from test
cross apply Nums
Where n <= len(Replace(strCol,'-',''))  

)
 Select *  into temp 
 from mycte

 Declare @sql nvarchar(max);
 Declare @cols varchar(max);

set @cols = STUFF((SELECT ',' + QUOTENAME(n)  
					FROM temp 
					group by n
					order by n
					FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') ;

 set @sql = N'SELECT ' + @cols + ' 
			FROM temp
			PIVOT (MAX(splitVal) FOR n IN (' + @cols + ')) p';

exec(@sql);

drop table temp 
drop table test

 

https://social.msdn.microsoft.com/Forums/en-US/9ee582ea-9ce2-4938-b727-5282cbbf5f87/string-manipulation?forum=transactsql&prof=required

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s