Query to select strings end with certain character

Question

I have a column that contains strings as:

aaa_1
aaa_11

I need to query strings that ends with _1. I tried the following: select col from table where col like %_1; But the query gives me the strings that ends with _1 and _11. How can I correct this ?

Answer 1

Try this:

select col from table where col like '%\_1'

character _ is a jolly, like %, but it matches only a single character, so you have to escape it with \

See here: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

Answer 2

You'll want to use something more substring related.

Try a where clause like:

SELECT col from table WHERE RIGHT(col, 2) = '_1'