mzy mzy - 3 months ago 12
MySQL Question

MySQL SELECT CASE WHEN column ends by number (using modulo)

I would like to add a column with

flag
when selecting data from MySQL table. The
flag
column should contain
0
value if column
key
(integer) is ended with
0, 1 or 5
.

Table (input):

key c2 c3 c4
223432 67 20131113 20
222900 83 20131113 5415
222828 22 20131113 7354
222483 43 20131114 20
222401 28 20131113 20


Table (expected output -
flag
column added):

flag key c2 c3 c4
1 223432 67 20131113 20
1 222900 83 20131113 5415
0 222828 22 20131113 7354
0 222483 43 20131114 20
1 222401 28 20131113 20


I know the modulo function can be used to determine the last number of
key
column.

SELECT key % 10 as x, s.* FROM shipment s;

Answer
SELECT case when key % 10 in (0,1,5) then 1 else 0 end as x, s.* FROM shipment s;