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 ?
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
You'll want to use something more substring related.
Try a where clause like:
SELECT col from table WHERE RIGHT(col, 2) = '_1'
Great answers!
My answer is an extension from the other posts.
In case you want to filter out data that is ending with specific character(s), use REGEXP operator.
Example : below query returns all strings where ending character(s) is/are vowel -
SELECT column_name
FROM table_name
WHERE column_name REGEXP ".*[a,e,i,o,u]{n}$" -- n = number of vowels at the end
You should escape % and _ by adding backslash \ as they are wildcards in mysql:
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
String | Description
\% | Matches one “%” character
\_ | Matches one “_” character
Many of these will pick up things like %_12 or %_111121, etc. Make sure you test out various cases to be sure these SELECT statements are giving you the proper subset.
SELECT col FROM table WHERE col REGEXP '_1[[:>:]]'
You can make a quick use of this query to filter out strings ending with specific character(s).
The below query output will give all names ending with 'er'.
select column_name
from table
where column_name regexp '.*er$';
%_1won't give you%_11, though%_1%would.WHERE RIGHT(the_column, 2) = '_1'