…Hi, a database ingenue here. I’m trying to figure out how to use
CASE in SQLite, or some equivalent approach. I’ve got a prod_names table that contains concatenated data–occasionally just 1 item, but usually containing several comma-separated items. For my new ‘Toy‘ column, I need to find every record that contains ‘CapGun‘. The code below works only when ‘CapGun‘ is the only item, and not when there are multiple items (eg, ‘BarbieDoll, CapGun, EasyBakeOven‘).
SELECT customer_id, prod_names, CASE prod_names WHEN 'CapGun' THEN 'CG' ELSE 'not_CG' END Toy FROM Toys_table ORDER BY Toy
I’ve tried various approaches like
WHEN LIKE '%CapGun%',
WHEN INSTR(prod_names,'CapGun') > 0, and
WHEN GLOB '*CapGun*' but they all return no results or throw a syntax error.
Any suggestions? I’m sure there must be a simple solution.
Use the expanded case syntax:
CASE WHEN prod_names LIKE '%CapGun%' THEN ... ELSE ... END
This lets use any expression as the condition in your
CASE, including other columns.
Answered By – MarcinJ
Answer Checked By – Senaida (BugsFixing Volunteer)