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

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?

From stackoverflow

• 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)

  TheJacobTaylor : Depending on your database and version, this might go through each row of the DB twice due to the 'OR' clause. I would make sure this is well indexed and carefully check out the explain plan.

  inerte : Indexes are useless if matched againt LIKE's starting %. http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html "... do not use indexes ... the LIKE value begins with a wildcard character."

  Jim Ferrans : @Jacob: have you ever encountered this in a production-grade database? Any non-broken query planner would scan myTable once and apply this where clause to each record.

• You could also do something like:

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

• 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.

  DBMarcos99 : What Josh said - sometimes you have to denormalise and add a column to dramatically improve performance. There is a REVERSE function you can use on text fields (for instance if you want to store email addresses but search on the domain eg .com .org etc), but I don't think it would help in terms of performance in the original setup here.