Use IF/CASE statement to find if a substring exist in a string in MYSQL

Question

I have a column which has a list of products in each row. e.g:

bat,ball,gloves
bat,gloves
shoes,gloves

I want to make a new column for each of the product, which will have a value of 1 or 0 depending on if that product exists in that row.

I'm using the following code right now:

select
*,
CASE WHEN product_name LIKE '%bat%' THEN 1 else 0 END AS bat,
CASE WHEN product_name LIKE '%gloves%' THEN 1 else 0 END AS gloves
from products

It does not work. Kindly help

you should read meta.stackoverflow.com/questions/333952/…

nbk
– nbk

2019-11-25 22:24:29 +00:00
Commented Nov 25, 2019 at 22:24 — nbk
– nbk, Commented Nov 25, 2019 at 22:24

GMB · Accepted Answer · 2019-11-25 22:22:25Z

1

You can use handy MysQL function find_in_set() to check if a value belongs to a comma-separated list:

select
    p.*,
    find_in_set('bat', product_name) > 0 bat
    find_in_set('gloves', product_name) > 0 gloves
from products p

If the value is found in the CSV list, find_in_set() returns a number between 1 and N that represents its position; else it returns 0 (or null if the searched CSV list is null). So basically, to check if a value belong to the CSV list, you just need to check if the value returned by find_in_set() is strictly above 0.

answered Nov 25, 2019 at 22:22

GMB

224k25 gold badges102 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hamza Khalid Over a year ago

Thanks. the function works but it always returns 0. I don't know why. My products are not seperated by commas (e.g: batglovesball). so I'm quering find_in_set('bat',product_name). I also tried '%bat%' but it does not work

Collectives™ on Stack Overflow

Use IF/CASE statement to find if a substring exist in a string in MYSQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related