Base on SQL column content insert the data into another table

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.

my table

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 CusmerId and Values like below format.

output table

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.

Answer

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.