Extract numeric part of string and get max value in column

Question

I have a table foo that stores codes in format lnnnnn where l is at least one letter and n is numeric value. Both letters or numbers can be of various length, so trying to solve this like mentioned here won't work.

Example:

 group | code
 =============
   1   | a0010
   1   | a0012
   1   | a0013
   2   | bn0014
   2   | bn0015
   2   | bn0016
   3   | u0017
   3   | u0018

My task is to get current highest numeric value of this column in desired group, to generate new number (like sequence).

Note that I cannot redesign table and explode string and text parts.

So far I tried:

select 
    max(code rlike '[0-9]$')
from 
    foo
where
    group = 2

but, sadly, regexp or rlike (synonyms) returns only 0 or 1 (matched or not matched).

Sadly, mysql's regex implementation doesn't allow you to extract any information from the match, as discussed in http://stackoverflow.com/questions/4021507/mysql-use-regex-to-extract-string — Peter Alfvin, Jul 17 '14 at 14:20
Yes, there's `cast` (or the implicit conversion shown in the answer below), but that's not the main problem you have. The main problem you have is that you don't know where the letters stop and the digits begin. — Peter Alfvin, Jul 17 '14 at 14:24
@ex3v What you're hearing from folks are various ways to determine where the numbers begin (e.g. numbers always start with zero, numbers are always four digits long, etc.). If you know specifics about the number, you can use a simple string operation to extract the numeric portion. — Peter Alfvin, Jul 17 '14 at 14:37
Come on guys, second sentence of my question: "Both letters or numbers can be of various length". — ex3v, Jul 18 '14 at 06:44

score 1 · Answer 1 · answered Jul 17 '14 at 14:12

One method is a brute force method:

select grp,
       max(case when substr(code, 1, 1) between '0' and '9' then code + 0
                when substr(code, 2, 1) between '0' and '9' then substr(code, 2) + 0
                when substr(code, 3, 1) between '0' and '9' then substr(code, 3) + 0
                when substr(code, 4, 1) between '0' and '9' then substr(code, 4) + 0
                when substr(code, 5, 1) between '0' and '9' then substr(code, 5) + 0
                when substr(code, 6, 1) between '0' and '9' then substr(code, 6) + 0
                when substr(code, 7, 1) between '0' and '9' then substr(code, 7) + 0
                when substr(code, 8, 1) between '0' and '9' then substr(code, 8) + 0
           end)                
from foo
group by grp;

@ex3v . . . It should be reasonable. `case` statements and basic string operations are not generally what cause performance issues in SQL. — Gordon Linoff, Jul 17 '14 at 14:22

score 1 · Answer 2 · answered Jul 17 '14 at 14:39

1

If your numeric codes is always four digits then you can do it like:

select groupid, max(right(code,4)) as maxcode
  from foo
 group by groupid

See it here on fiddle: http://sqlfiddle.com/#!2/775b3/2

answered Jul 17 '14 at 14:39

Jorge Campos

22,647
7
56
87

Quoting second sentence from my question: "Both letters or numbers can be of various length" – ex3v Jul 18 '14 at 06:45

score 0 · Answer 3 · answered Jul 17 '14 at 14:29

0

If all numeric parts start with a 0:

select gp, max(cast(substr(code, instr(code, '0')) as unsigned))
from t
group by gp

See sqlfiddle

If not, for arbitrary numeric parts (that start with any digit):

select gp, max(cast(substr(code, instr(code, n)) as unsigned))
from t
join (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5
     union select 6 union select 7 union select 8 union select 9) x
group by gp

See sqlfiddle

answered Jul 17 '14 at 14:29

Bohemian

412,405
93
575
722

Quoting second sentence from my question: "Both letters or numbers can be of various length" – ex3v Jul 18 '14 at 06:46
Have tried either query? Neither query assumes any lengths of parts. – Bohemian Jul 18 '14 at 07:59
First one doesn't fit. Tried second one and, unfortunately, failed... So far I have two groups, let's say `A` and `G`. Max values for them are `A => a118691`, `B => g21127`. Your query returned: `A => 118691`, `B => 119530`. – ex3v Jul 18 '14 at 08:15
Are you sure there's no row `where code like '%119530'` for B? – Bohemian Jul 18 '14 at 10:07
Unfortunately no. Max (for now) `B` group integer value is around 25000 – ex3v Jul 18 '14 at 12:20

Extract numeric part of string and get max value in column

3 Answers3