CODEWITHSUNDEEP
mysql
user1810868
user1810868

Reputation: 1615

Query to select strings end with certain character

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 ?

Upvotes: 25

Views: 75902

Answers (7)

Rishabh Barman
Rishabh Barman

Reputation: 569

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

Upvotes: 1

blackhole
blackhole

Reputation: 204

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$';

Upvotes: 0

Elaine
Elaine

Reputation: 1

SELECT col FROM table WHERE col REGEXP '_1[[:>:]]'

Upvotes: 0

Jonathan
Jonathan

Reputation: 1

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.

Upvotes: 0

Mehdi Karamosly
Mehdi Karamosly

Reputation: 5438

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

Upvotes: 0

fthiella
fthiella

Reputation: 49089

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

Upvotes: 43

Adam Hoffman
Adam Hoffman

Reputation: 793

You'll want to use something more substring related.

Try a where clause like:

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

Upvotes: 29

Related Questions