sfmc sql query to update a data extension field using using like function in the same data extension

Question

I am working on a slightly complex scenario and need your help figuring out the correct approach. I have data extension contains 3 columns (product family, product code, product description) each family of products have submodels that are provided in the description field. for example product xx basic, xx pro, xx ultimate. im trying to write a query using LIKE function that searches for xx in the description field and updates the family field on the same data extension.

it would look like something like below:

product family| product code | product description
--------------------------------------------------
xx            | XXP502       | XX basic plan
--------------------------------------------------
xx            | XXP512       | XX pro plan
--------------------------------------------------
xx            | XXP522       | XX ultimate plan

im new to sfmc sql and would really appreciate it if you can help me write this.

Answer 1

IMHO, you may want to try to use Excel sheet to process the data instead of SQL since somehow I think it might be faster.

In SQL, you should create a separate DE first then use SQL query to populate data into this DE.

Use CASE expression in SQL to return a value based on conditions.

For product family name: my idea would be I use CHARINDEX to find the index of the product plan, then get the product family name by using SUBSTRING

For product code: it would be the same like the way for product family name, but concentrate with product code (corresponding with its product plan?)

SELECT
CASE
    WHEN CHARINDEX('basic plan', [product description]) > 0 THEN SUBSTRING([product description], 1, CHARINDEX('basic plan', [product description]) -2)
    WHEN CHARINDEX('pro plan', [product description]) > 0 THEN SUBSTRING([product description], 1, CHARINDEX('pro plan', [product description]) -2)
    WHEN CHARINDEX('ultimate plan', [product description]) > 0 THEN SUBSTRING([product description], 1, CHARINDEX('ultimate plan', [product description]) -2)
END AS [product family],
CASE
    WHEN CHARINDEX('basic plan', [product description]) > 0 THEN CONCAT(SUBSTRING([product description], 1, CHARINDEX('basic plan', [product description]) -2), 'P502')
    WHEN CHARINDEX('pro plan', [product description]) > 0 THEN CONCAT(SUBSTRING([product description], 1, CHARINDEX('pro plan', [product description]) -2), 'P512')
    WHEN CHARINDEX('ultimate plan', [product description]) > 0 THEN CONCAT(SUBSTRING([product description], 1, CHARINDEX('ultimate plan', [product description]) -2), 'P522')
END AS [product code],
[product description]
FROM
[DE name]

References:

• CASE expression
• CHARINDEX function
• CONCAT function
• SUBSTRING function

Answer 2

Couldn't you just do a simple string replacement on product description to isolate the product family values?

CREATE TABLE Products
    ([product code] varchar(50), [product description] varchar(100))
;
INSERT INTO Products
     ([product code], [product description])
VALUES
 ('XXP502','XX basic plan')
,('XXP512','XX pro plan')
,('XXP522','XX ultimate plan')

select
  ltrim(replace(replace(replace(p.[product description],'basic plan',''),'pro plan',''),'ultimate plan','')) [product family]
, [product code]
, [product description]
from Products p

Output:

product family	product code	product description
XX	XXP502	XX basic plan
XX	XXP512	XX pro plan
XX	XXP522	XX ultimate plan

Try it yourself: fiddle