Lee Lee - 7 months ago 22
SQL Question

MYSQL - IF column contains character THEN ... ELSE

I have a column

product_id
that can contain an underscore.If it does,i want to grab everything to the left of it,like so:

SELECT LEFT(product_id,LOCATE('_',product_id) - 1) As po, product_id FROM orders_item


This produces the following:

po product_id
40 40_59
46 46_74
95 95_223
134 134_271
86 86_265
71
76
48 48_79
137 137_298
50 50_247
48 48_80
124 124_187


However it ignores any records that don't have an underscore.How do I write the following in MySQL?

If `<contains underscore>` THEN `<everything to the left of the underscore>` ELSE `<entire field>`

Answer

Use SUBSTRING_INDEX instead:

SELECT SUBSTRING_INDEX(product_id, '_', 1) AS po, product_id
FROM orders_item
Comments