CODEWITHSUNDEEP
mysqlstringsubstr
myforwik
myforwik

Reputation: 119

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

Upvotes: 0

Views: 318

Answers (4)

yvan
yvan

Reputation: 958

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

Upvotes: 2

miki
miki

Reputation: 695

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

Upvotes: 0

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36671

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;

Upvotes: 0

sberry
sberry

Reputation: 132138

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

Upvotes: 1

Related Questions