I have below table name: CustomerContent with the below image data and
My product is:is common in ProductContent cell. First colon no need to split (
My product is:) if next colons we need to split the ProductContent Cell base text assign the values like below. If slip data content
CGM then assign value 37.
CustomerId ProductContent 100 My product is: Shoes 101 My product is: Diabetic Shoes 102 My product is: Shoes Back Brace 103 My product is: Dexcom G6 (CGM) 104 My product is: Freestyle Libre (CGM) 105 My product is: Shoes Knee Brace 106 My product is: Dexcom G6 (CGM): Freestyle Libre (CGM): Diabetic Shoes 107 My product is: Dexcom G6 (CGM): Freestyle Libre (CGM) 108 My product is: Freestyle Libre (CGM): Diabetic Shoes
I need output like below and insert above data into another table name: CustomerContentTemp contain columns
Values like below format.
CustomerId Values 100 1 101 1 102 8 103 37 104 37 105 14 106 37 106 37 106 1 107 37 107 37 108 37 108 1
From below data logic for inserting into output CustomerContentTemp table
Shoes=1 Diabetic Shoes=1 Shoes Back Brace=8 Dexcom G6 (CGM)=37 Freestyle Libre (CGM)=37 Shoes Knee Brace=14
IF ProductContent cell data is not match then insert value 0.
My personal choice for a t-sql splitter is this one. https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2 There are plenty of other choices and many have advantages and disadvantages. This one for me is the most robust with the least overhead and confusion. To find the code yourself you will need to scroll to the bottom of the article (ideally reading the whole thing so you understand what it is doing).
Now let’s take your data and make it consumable so anybody can just copy and paste it. This article (How Stuff and ‘For Xml Path’ work in SQL Server?) will walk you through that part of the process.
create table CustomerContent ( CustomerId int , ProductContent varchar(500) ) insert CustomerContent select 100, 'My product is: Shoes' union all select 101, 'My product is: Diabetic Shoes' union all select 102, 'My product is: Shoes Back Brace' union all select 103, 'My product is: Dexcom G6 (CGM)' union all select 104, 'My product is: Freestyle Libre (CGM)' union all select 105, 'My product is: Shoes Knee Brace' union all select 106, 'My product is: Dexcom G6 (CGM): Freestyle Libre (CGM): Diabetic Shoes' union all select 107, 'My product is: Dexcom G6 (CGM): Freestyle Libre (CGM)' union all select 108, 'My product is: Freestyle Libre (CGM): Diabetic Shoes' create table CustomerContentTemp ( SimpleName varchar(100) , Result int ) insert CustomerContentTemp select 'Shoes', 1 union all select 'Diabetic Shoes', 1 union all select 'Shoes Back Brace', 8 union all select 'Dexcom G6 (CGM)', 37 union all select 'Freestyle Libre (CGM)', 37 union all select 'Shoes Knee Brace', 14
OK. So now have nice and easy to work with data and a splitter function. Here is the first step in your puzzle. You have to split this data apart and get rid of that oddball prefix.
select c.* , cct.SimpleName , cct.Result from CustomerContent c cross apply dbo.DelimitedSplit8K_LEAD(replace(c.ProductContent, 'My product is: ', ''), ':') x left join CustomerContentTemp cct on cct.SimpleName = ltrim(x.Item)
From this result you will need to apply the logic for using STUFF to get this squished back into the denormalized format you need.