mysql match on a string subset

With table:

idx | int | ident
1   | 10  | Foo 001_10
2   | 10  | Farfoo 002_11
3   | 11  | Sofoo 001_11
4   | 10  | Lafoo 001_10
5   | 10  | MoreFoo 001_11

How can I select all rows where the last 2 characters in 开发者_运维问答the string 'ident' do not match the integer in the 'int' coloumn? Which for the table above would be:

2   | 10  | Farfoo 002_11
5   | 10  | MoreFoo 001_11

You should use the SUBSTRING function, you will have something like that

SELECT * FROM table WHERE SUBSTRING(ident, -2) != int

But I'm not sure if it the best way to reach your goal. Substring can be a very expensive method for MySQL, maybe someone else knows faster and better method.

Here a better way, that allows you to get everything after the last _, because I'm not sure that you gonna always have two digit after _. For that case, I'm using SUBSTRING_INDEX.

SELECT * FROM table WHERE SUBSTRING_INDEX(ident, '_', -1) != int

SELECT idx, `int`, `ident` FROM table WHERE SUBSTRING(`ident`, -2, 2) != `int`;

You can also use RIGHT string function using right(ident,2) you will get the last two digits from string then compare it will int column if both are unequal then retrieve the not matching contents.

select * from tablename
where RIGHT(ident,2) != int;

select * from whatever where not ident like concat('%', cast `int` as varchar);