Agfdhyk

I'm sure I'm doing this wrong, but I can't find what the common-sense alternative is.

My input is a complicated long string field that I need to break out into other fields.

I was using a case statement, but it's ballooning into hundreds of variations. Basically repeated versions of what's below except each of them are growing to hundreds of lines long. It's a pain to update when things change and get added.

SET `BrandName` =

CASE

  WHEN `SaleString` like '%Posh%'     THEN 'Posh Purchaser'

  WHEN `SaleString` like '%DFM%'      THEN 'Deep Fried Meat'

  WHEN `SaleString` like '%Glam%'     THEN 'Glamour Girl'

  -- [WHEN search_condition THEN statement_list] ...

  ELSE NULL

END

Is there a way to use a translation table that contains to substring/value pairs?

Some thing like...

UPDATE `Sales`

SET `BrandName` = `translation`.`value`

WHERE '%substring%' = `translation`.`clue`;

Edit:

This answer from Bill works perfectly for me. Thanks ya'll.

UPDATE Sales s

JOIN Translation t ON s.SaleString LIKE CONCAT('%', t.clue, '%')

SET s.BrandName = t.value

You need to use a join to your translation table to match each SaleString to the right row. Once you do that, set the BrandName to the corresponding value.

UPDATE Sales s

JOIN Translation t ON s.SaleString LIKE CONCAT('%', t.clue, '%')

SET s.BrandName = t.value

Note that this will do something slightly different from your CASE solution, if there are any SaleString values that match multiple patterns. Your CASE solution would set the BrandName based on the first matched pattern, but the JOIN solution will match to all of the patterns, and set the BrandName to each one, one by one.

Example: If your SaleString is "Posh Glam", it matches two patterns. The JOIN will process the SaleString twice, once for each match. First it will set the BrandName to "Posh Purchaser" on that row of Sales, then it will find the second match and overwrite the BrandName to "Glamour Girl".

Most simple and something which costs less maintenance or changing complete switches.

Would be to create a (temporary) table where you keep the patterns and replaces.

If somethings changes you can just INSERT, UPDATE or DELETE the records from the table.

Create table and insert's

CREATE TABLE update_sales (



    pattern VARCHAR(255) 

  , replace_with VARCHAR(255)

); 



INSERT INTO update_sales (pattern, replace_with) VALUES('Posh', 'Posh Purchaser');

INSERT INTO update_sales (pattern, replace_with) VALUES('DFM', 'Deep Fried Meat');

INSERT INTO update_sales (pattern, replace_with) VALUES('Glam', 'Glamour Girl'); 

And use the following query..

Query

UPDATE 

 Sales

INNER JOIN

 update_sales

ON

 Sales.SaleString LIKE CONCAT('%', update_sales.pattern , '%')

SET 

 Sales.SaleString = update_sales.replace_with

Results

    SELECT * FROM Sales;



| SaleString      |

| --------------- |

| Posh Purchaser  |

| Deep Fried Meat |

| Glamour Girl    |

demo