Split single column in rows separted out by mulitple columns according to string - SQL Server

Question

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.

Answer 1

If open to a Table-Valued helper Function. I modified a split/parse function to accept two non-like delimeters because I tired of extracting strings (left,right,charindex,patindex,etc).

Example or dbFiddle

Select SKU
      ,Year  = B.RetVal
      ,Make  = C.RetVal
      ,Model = D.RetVal
 From  Table1 A
 Cross Apply  [dbo].[tvf-Str-Extract]('/'+A.Conca+'/','Year;','/')  B
 Cross Apply  [dbo].[tvf-Str-Extract]('/'+A.Conca+'/','Make;' ,'/') C
 Cross Apply  [dbo].[tvf-Str-Extract]('/'+A.Conca+'/','Model;','/') D


Select SKU2
      ,Year  = XMLData.value('tr[1]/td[1]','varchar(max)')
      ,Make  = XMLData.value('tr[1]/td[2]','varchar(max)')
      ,Model = ltrim(rtrim(left(XMLData.value('tr[1]/td[3]','varchar(max)'),charindex(';',XMLData.value('tr[1]/td[3]','varchar(max)')+';')-1)))
      ,Notes = ltrim(rtrim(substring(XMLData.value('tr[1]/td[3]','varchar(max)'),charindex(';',XMLData.value('tr[1]/td[3]','varchar(max)')+';')+1,100)))
 From  Table2
 Cross Apply (values (cast(Conca2 as xml)) )B(XMLdata)

Returns

The TVF if Interested

CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table 
As
Return (  

with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
       cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
       cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
       cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)

Select RetSeq = Row_Number() over (Order By N)
      ,RetPos = N
      ,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1) 
 From  (
        Select *,RetVal = Substring(@String, N, L) 
         From  cte4
       ) A
 Where charindex(@Delimiter2,RetVal)>1

)
/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/

Edit - You can use the TVF for table2 as well

Select SKU2
      ,B.Year
      ,B.Make
      ,Model = ltrim(rtrim(left(B.Model,charindex(';',B.Model+';')-1)))
      ,Notes = ltrim(rtrim(substring(B.Model,charindex(';',B.Model+';')+1,100)))
 From  Table2 A
 Cross Apply (
                Select Year  = max(case when RetSeq=1 then RetVal end)
                      ,Make  = max(case when RetSeq=2 then RetVal end)
                      ,Model = max(case when RetSeq=3 then RetVal end)
                 From [dbo].[tvf-Str-Extract](A.Conca2,'<td>','</td>')  
             )B