I have 2 tables requiring string split. The result table will split the string to create new year, make, model columns. Here are the sample 2 tables.
CREATE TABLE Table1
(
sku varchar(50),
Conca varchar(max)
);
INSERT INTO Table1 (sku, Conca)
VALUES ('AVS1234','Game;Consoles/Year;2001/ Year;2002/ Year;2006/ Year;2007/ Year;2009/ Year;2011/Make;XBox/Model;100/'),
('AVS1234','Game;Consoles/Year;2016/Make;Nintendo/Model;DX/'),
('AVS1234','Game;Consoles/Year;2001/ Year;2002/ Year;2006/ Year;2007/ Year;2009/ Year;2011/Make;PS/Model;300/Model;500/');
CREATE TABLE Table2
(
sku2 varchar(50),
Conca2 varchar(max)
);
INSERT INTO Table2 (sku2, Conca2)
VALUES ('AVS1234','<tr><td>2011 </td><td>Xbox </td><td>100; Notes : 2 pc.; Stainless</td></tr>'),
('AVS1234','<tr><td>2005 </td><td>Xbox </td><td>100; Notes : 2 pc.; Stainless</td></tr>'),
('AVS1234','<tr><td>2016 </td><td>Xbox </td><td>300</td></tr>');
Intended result table1
Sku year make model
--------------------------
avs1234 2001 XBox 100
avs1234 2002 XBox 100
avs1234 2006 XBox 100
avs1234 2007 XBox 100
avs1234 2009 XBox 100
avs1234 2011 XBox 100
AVS1234 2016 Nintendo DX
AVS1234 2001 PS 300
AVS1234 2002 PS 300
AVS1234 2006 PS 300
AVS1234 2007 PS 300
AVS1234 2009 PS 300
AVS1234 2011 PS 300
AVS1234 2001 PS 500
AVS1234 2002 PS 500
AVS1234 2006 PS 500
AVS1234 2007 PS 500
AVS1234 2009 PS 500
AVS1234 2011 PS 500
Intended result table2
Sku year make model Notes
------------------------------------
avs1234 2001 XBox 100 2 pc.; Stainless
avs1234 2002 XBox 100 2 pc.; Stainless
AVS1234 2005 Xbox 100 2 pc.; Stainless
AVS1234 2016 Xbox 300
First I thought to try the position/trim method of:
Cross Apply
(Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
But the # years could range from 1950s to 2018 and beyond. So this will create way too many "pos".
To the best of my capabilities I then tried:
with firstpass as
(
SELECT A.sku,
Split.a.value('.', 'VARCHAR(100)') AS Data
FROM
(SELECT sku,
Cast('<M>' + replace((Select replace(Conca,'year','§§Split§§') as [*] For XML Path('')),'§§Split§§','</M><M>')+'</M>' as xml) as Data
FROM table1) AS A
CROSS APPLY
Data.nodes ('/M') AS Split(a)
)
SELECT
*, Year = SUBSTRING([data], 1 ,
case when CHARINDEX('/', [data] ) = 0 then LEN([data])
else CHARINDEX('/', [data]) -1 end)
from firstpass
I can't quite figure out where to go next.... Please assist.