MySQL: return max of the last 5 digits from column

Question

just need help with regular expression in mysql query please:

column ISRC
GBCQV0700378
GBCQV0700382
GBCQV0700387

i need 00387 returned to me (the biggest number) i'm using:

SELECT max(ISRC) REGEXP "/(\d{5})$/" FROM digital_info; // returns 0

SELECT max(ISRC) FROM digital_info as m where (ISRC REGEXP "/(\d{5})$/"); // returns NULL

please?

thanks for those two who answered already - those would be the solutions for above. But now i realised i need it a bit more complicated:

Not all ISRC's got GBCQV prefix, but i only need MAX from GBCQV prefix?

column ISRC
GBCQV0700378
SOME00125963
GBCQV0700382
GBCQV0700387

but i still need 00387 returned to me please

score 2 · Answer 1 · answered Nov 30 '11 at 16:14

2

Use the string-based RIGHT() function:

SELECT MAX(RIGHT(ISRC, 5)) ...

answered Nov 30 '11 at 16:14

Marc B

356,200
43
426
500

it never occur to me just to use RIGHT()!! need some rest from coding that's for sure... thanks!! – Elen Nov 30 '11 at 16:17

Tom van der Woerdt · Accepted Answer · 2011-11-30T16:24:37.970

0

SELECT MAX(SUBSTRING(ISRC, -5)) FROM digital_info WHERE LEFT(ISRC) = "GBCQV";

edited Nov 30 '11 at 16:24

answered Nov 30 '11 at 16:14

Tom van der Woerdt

29,532
7
72
105

thanks for the answer, but i just realised i need more complicated than i just asked. i have edited my original post – Elen Nov 30 '11 at 16:23

MySQL: return max of the last 5 digits from column

2 Answers2

Linked