SQL Server split a single column multiple times

Question

I have a database table in which there is a column with complex data with two levels with the column that I want to split. Here is an example of data (data changed to protect the innocent :):

Table

ID = varchar(100) CarData = varchar(1000) ID CarData 1 Nissan:blue:20000,Ford:green:10000 2 Nissan:steel:20001,Ford:blue:10001,Chevy:blue:10000,Ford:olive:10000 ** Note that cardata can is not fixed, and can have many cars in it 

Desired Result:

 ID Manufacture Color Cost 1 Nissan Blue 20000 1 Ford green 10000 2 Nissan steel 20001 ... and on 

Therefore, to say that I just need to split the first folded field, which is a comma, and create a row for it, and then break the second stacked field, which is a colon into columns.

Any help would be greatly appreciated.

Answer 1

 -- Sample data declare @T table(ID int, CarData varchar(100)) insert into @T values (1, 'Nissan:blue:20000,Ford:green:10000'), (2, 'Nissan:steel:20001,Ford:blue:10001,Chevy:blue:10000,Ford:olive:10000') -- Recursice CTE to get one row for each car ;with cte(ID, Car, CarData) as ( select ID, cast(substring(CarData+',', 1, charindex(',', CarData+',')-1) as varchar(100)), stuff(CarData, 1, charindex(',', CarData), '')+',' from @T where len(CarData) > 0 union all select ID, cast(substring(CarData, 1, charindex(',', CarData)-1) as varchar(100)), stuff(CarData, 1, charindex(',', CarData), '') from cte where len(CarData) > 0 ) -- Use parsename to split the car data select ID, parsename(replace(Car, ':', '.'), 3) as Manufacture, parsename(replace(Car, ':', '.'), 2) as Color, parsename(replace(Car, ':', '.'), 1) as Cost from cte order by ID 

Result:

 ID Manufacture Color Cost -- ----------- ------ ----- 1 Nissan blue 20000 1 Ford green 10000 2 Nissan steel 20001 2 Ford blue 10001 2 Chevy blue 10000 2 Ford olive 10000 

Change 1

You will have problems with parsename if the color, cost or manufacturer name contains . . If so, you should try this.

 -- Sample data declare @T table(ID int, CarData varchar(100)) insert into @T values (1, 'Nissan:blue:20000,Ford:green:10000'), (2, 'Nissan:steel:20001,Ford:blue:10001,Chevy:blue:10000,Ford:olive:10000') -- Recursice CTE to get one row for each car ;with cte(ID, Car, CarData) as ( select ID, cast(substring(CarData+',', 1, charindex(',', CarData+',')-1) as varchar(100)), stuff(CarData, 1, charindex(',', CarData), '')+',' from @T where len(CarData) > 0 union all select ID, cast(substring(CarData, 1, charindex(',', CarData)-1) as varchar(100)), stuff(CarData, 1, charindex(',', CarData), '') from cte where len(CarData) > 0 ) -- Split the car data with substring select ID, substring(Car, 1, P1.Pos-1) as Manufacture, substring(Car, P1.Pos+1, P2.Pos-P1.Pos-1) as Color, substring(Car, P2.Pos+1, len(Car)-P2.Pos) as Cost from cte cross apply (select charindex(':', Car)) as P1(Pos) cross apply (select charindex(':', Car, P1.Pos+1)) as P2(Pos) order by ID 

Answer 2

Use this split function to create a result table.

First, I would call dbo.split() with , as a delimiter character. You will then have a list of items such as:

 Nissan:blue:20000 Ford:green:10000 Nissan:steel:20001 Ford:blue:10001 Chevy:blue:10000 Ford:olive:10000 

From there, you can call dbo.split() again, using : as a separator. Each call will lead to the formation of exactly three records (provided that your design is at least “normal”).

As @JNK noted in his comment, we hope this is not what you want to run regularly.

EDIT:

Sample code to run:

 SELECT * INTO #YuckyCar FROM ( SELECT 1 ID, 'Nissan:blue:20000,Ford:green:10000' CarData UNION SELECT 2, 'Nissan:steel:20001,Ford:blue:10001,Chevy:blue:10000,Ford:olive:10000' ) T; -- Shows logical step #1 SELECT ID, X.items MoreCarData FROM #YuckyCar CROSS APPLY dbo.Split(CarData, ',') X; -- Shows logical step #2 SELECT Q.ID, Y.items FROM ( SELECT ID, X.items MoreCarData FROM #YuckyCar CROSS APPLY dbo.Split(CarData, ',') X) Q CROSS APPLY dbo.Split(Q.MoreCarData, ':') Y DROP TABLE #YuckyCar; 

The problem in the last part is that you cannot guarantee line 1 = Manufacturer, line 2 = Color, line 3 = Cost.

Answer 3

This should solve your problem:

[EDIT] . Your identifier is varchar (100), and you will not indicate if it is the primary key, so I made some changes ... The identifier should not be the primary key in this case.

 declare @T table(ID varchar(100), CarData varchar(1000)) declare @OUT table(pk INT IDENTITY(1,1), ID varchar(100), Manufacture varchar(100), Color VARCHAR(100), Cost INT) insert into @T (ID, CarData) values ('1', 'Nissan:blue:20000,Ford:green:10000'), ('2', 'Nissan:steel:20001,Ford:blue:10001,Chevy:blue:10000,Ford:olive:10000') DECLARE @x XML, @i INT, @ID VARCHAR(100), @maxi INT; ;WITH list AS (SELECT pk = ROW_NUMBER() OVER(ORDER BY ID), * FROM @T) SELECT @i=1, @maxi=MAX(pk) FROM list; WHILE @i <= @maxi BEGIN ;WITH list AS (SELECT pk = ROW_NUMBER() OVER(ORDER BY ID), * FROM @T) SELECT @x = CAST( '<root><car><prop>' + REPLACE( REPLACE( CarData ,':' ,'</prop><prop>' ) ,',' ,'</prop></car><car><prop>' ) + '</prop></car></root>' AS XML) , @ID = ID FROM list WHERE pk = @i INSERT INTO @OUT SELECT ID = @ID ,Manufacture = x.value('./prop[1]','VARCHAR(100)') ,Color = x.value('./prop[2]','VARCHAR(100)') ,Cost = x.value('./prop[3]','INT') FROM @x.nodes('/root/car') AS T(x) SET @i = @i + 1; END SELECT * FROM @OUT /* -- OUTPUT ID Manufacture Color Cost -------------------------------- 1 Nissan blue 20000 1 Ford green 10000 2 Nissan steel 20001 2 Ford blue 10001 2 Chevy blue 10000 2 Ford olive 10000 */