CODEWITHSUNDEEP
mysql
ManOAction
ManOAction

Reputation: 403

Alternative to Complicated Case Statement

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

Upvotes: 1

Views: 109

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562348

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".

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

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

Upvotes: 1

Related Questions