Check strings and add new column in SQL

Question

I'm looking for a code that checks whether or not it's an animal.

My table looks like this:

id	test
1	fish_test
1	shoe
1	tiger_test
1	shirt
1	test_bird

What I want to get is this table:

id	test	group
1	fish_test	1
1	shoe	0
1	tiger_test	1
1	shirt	0
1	test_bird	1

So if the the column test contains the string test, it should set the new column to 1, else to 0.

How would this could or If condition look like in the select statement?

Answer 1

You can use a case expression:

SELECT mytable.*, CASE WHEN test LIKE '%test%' THEN 1 ELSE 0 END
FROM   mytable