How do you select from mysql where last character in a string = x?

Question

I'd like to select rows from the database where the last character in the mov_id column equals to 1 and 2.

How would the query look like?

Answer 1

SELECT * FROM `myTable` WHERE `mov_id` LIKE '%1' OR `mov_id` LIKE '%2'

the % character is a wildcard which matches anything (like * in many other places)

Answer 2

You could also do something like:

select
     your, fields, go, here
from table
where
     substring(mov_id, (char_length(move_id) - 1)) = x

Answer 3

If mov_id is a numeric value (TINYINT, INT, etc...) then you should use a numeric operator. For instance, use the modulo operator to keep the last digit

SELECT * FROM mytable
WHERE (mov_id MOD 10) IN (1, 2)

If mov_id is a string, you can use LIKE or SUBSTRING(). SUBSTRING() will be slightly faster.

SELECT * FROM mytable
WHERE SUBSTRING(mov_id, -1) IN ('1', '2')

If your table is big or that query is frequently run, you should definitely consider adding a column to your table, in which you would store mov_id's last digit/character, and index that column.