CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Michiel van Dijk
Michiel van Dijk

Reputation: 953

MySQL - Assign numbers based on value

I'm trying to replicate what I'm achieving with the code below, but without using CASE:

CASE
WHEN c.Path LIKE '/Local/Activities%' THEN 2
WHEN c.Path LIKE '/Local/Automotive%' THEN 3
WHEN c.Path LIKE '/Local/Beauty & Spas%' THEN 4
WHEN c.Path LIKE '/Local/Food & Drink%' THEN 5
WHEN c.Path LIKE '/Local/Health & Fitness%' THEN 6
WHEN c.Path LIKE '/Local/Personal Services%' THEN 7
ELSE 8
END AS Category

I have 6 main Categories, which I have assigned numbers to with this CASE. I want to do the same for my sub Categories, however, I have about 100 of those, which would make writing a CASE quite tiresome, and prone to mistake.

How would I go about assigning those sub Categories number values without using CASE and without having to write a huge amount of code?

I have tried to find a solution here or elsewhere online, but have yet to find anything relating to my question.

NOTE: I only have Read access to this database.

Upvotes: 0

Views: 126

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can define a lookup table:

create table pathCategories (
    pathCategoryId int auto_increment primary key,
    prefix varchar(255)
);

insert into pathCategories (prefix)
    values ('/Local/Activities'),
           ('/Local/Automotive'),
           . . .;

Then the query would look like:

select t.*, pc.pathCategory
from t left join
     pathCategories pc
     on t.path like concat(pc.prefix, '%');

Upvotes: 1

Related Questions