CODEWITHSUNDEEP
mysql
user15063
user15063

Reputation:

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?

Upvotes: 11

Views: 39360

Answers (6)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

Try this way too:

SELECT field1
FROM table
WHERE RIGHT(field1, 1) = 'x'

it displays the fields that has last a value of x.

Upvotes: 4

ajay verma
ajay verma

Reputation: 359

SELECT *
FROM Table
WHERE RIGHT(Column_name, 1) IN ('x')

if you want to match two character just replace 1 by 2. In general:

RIGHT(COLUMN_NAME, NO_OF_CHARACTER_YOU WANT_TO_MATCH_FROM_LAST)

And if you want to match the starting char just use LEFT instead of RIGHT

Upvotes: 2

Ravean
Ravean

Reputation: 89

SELECT * FROM table WHERE mov_id REGEXP '1$' OR mov_id REGEXP '2$'

Upvotes: -1

Josh Davis
Josh Davis

Reputation: 28730

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.

Upvotes: 9

Mr. Smith
Mr. Smith

Reputation: 5558

You could also do something like:

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

Upvotes: -1

nickf
nickf

Reputation: 546143

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)

Upvotes: 26

Related Questions