SQLite – LIKE vs IN Operator?

October 22, 2022

Is there a better way to write this?

Basically, I wanted to achieve a similar result as seen in the screenshot but with the LIKE operator. However, when I use the % wildcard the table only includes the first string query. I managed to get the result anyway with the IN operator but I would like to understand how to get the same result with the LIKE operator if it is indeed possible.

    %%sql
SELECT Mission_Outcome, count(Mission_Outcome) as Count
FROM SPACEXTBL
WHERE Mission_Outcome LIKE '%Success%' or  '%Failure%'
GROUP BY Mission_Outcome 
ORDER BY Count DESC;

%%sql
SELECT Mission_Outcome, count(Mission_Outcome) as Count
FROM SPACEXTBL
WHERE Mission_Outcome IN ('Success', 'Failure (in flight)', 'Success (payload status unclear)')
GROUP BY Mission_Outcome 
ORDER BY Count DESC;

SQL-table

>Solution :

You would need to repeat the LIKE keyword for each comparison:

SELECT Mission_Outcome, COUNT(Mission_Outcome) AS Count
FROM SPACEXTBL
WHERE Mission_Outcome LIKE '%Success%' OR Mission_Outcome LIKE '%Failure%'
GROUP BY Mission_Outcome 
ORDER BY Count DESC;

If your version of SQLite have the REGEXP UDF installed, you could use an alternation here:

SELECT Mission_Outcome, COUNT(Mission_Outcome) AS Count
FROM SPACEXTBL
WHERE Mission_Outcome REGEXP 'Success|Failure'
GROUP BY Mission_Outcome 
ORDER BY Count DESC;