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 stackoverflow.com/questions/4021507/… — Peter Alfvin
– Peter Alfvin, Commented Jul 17, 2014 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
– Peter Alfvin, Commented Jul 17, 2014 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
– Peter Alfvin, Commented Jul 17, 2014 at 14:37

Gordon Linoff · Accepted Answer · 2014-07-17 14:12:20Z

1

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;

answered Jul 17, 2014 at 14:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@ex3v . . . It should be reasonable. case statements and basic string operations are not generally what cause performance issues in SQL.

Jorge Campos · Accepted Answer · 2014-07-17 14:39:58Z

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, 2014 at 14:39

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

1 Comment

ex3v Over a year ago

Quoting second sentence from my question: "Both letters or numbers can be of various length"

Bohemian · Accepted Answer · 2014-07-17 14:29:01Z

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, 2014 at 14:29

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

5 Comments

ex3v Over a year ago

Quoting second sentence from my question: "Both letters or numbers can be of various length"

Bohemian Over a year ago

Have tried either query? Neither query assumes any lengths of parts.

ex3v Over a year ago

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.

Bohemian Over a year ago

Are you sure there's no row where code like '%119530' for B?

ex3v Over a year ago

Unfortunately no. Max (for now) B group integer value is around 25000

Collectives™ on Stack Overflow

Extract numeric part of string and get max value in column

3 Answers 3

1 Comment

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related