How to find MAX() value of character column?

Question

We have legacy table where one of the columns part of composite key was manually filled with values:

code
------
'001'    
'002'    
'099'

etc.

Now, we have feature request in which we must know MAX(code) in order to give user next possible value, in example case form above next value is '100'.

We tried to experiment with this but we still can't find any reasonable explanation how DB2 engine calculates that

MAX('001', '099', '576') is '576'

MAX('099', '99', 'www') is '99' and so on.

Any help or suggestion would be much appreciated!

Solution 1

You already have the answer to getting the maximum numeric value, but to answer the other part with regard to 'www','099','99'.

The AS/400 uses EBCDIC to store values, this is different to ASCII in several ways, the most important for your purposes is that Alpha characters come before numbers, which is the opposite of Ascii.

So on your Max() your 3 strings will be sorted and the highest EBCDIC value used so

• 'www'
• '099'
• '99 '

As you can see your '99' string is really '99 ' so it is higher that the one with the leading zero.

Solution 2

Cast it to int before applying max()

Solution 3

This max working well in your type definition, when you want do max on integer values then convert values to integer before calling MAX, but i see you mixing max with string 'www' how you imagine this works?

Filter integer only values, cast it to int and call max. This is not good designed solution but looking at your problem i think is enough.

Solution 4

For the numeric maximum -- filter out the non-numeric values and cast to a numeric for aggregation:

SELECT MAX(INT(FLD1)) 
WHERE FLD1 <> ' ' 
AND TRANSLATE(FLD1, '0123456789', '0123456789') = FLD1

SQL Reference: TRANSLATE

---

And the reasonable explanation:

SQL Reference: MAX

View more solutions

Comments

[zarko.susnjar]

Yes, I've add 'www' just to see how this works, if it maybe worked with converting chars to ascii, or comparing string lengths etc. I couldn't find how this works on IBM website so i thought someone here knows how it works.

[zarko.susnjar]

i think you answered exactly what I was asking myself. I didn't write well my question (others, sorry). I knew I can cast value to numeric type, I was just wondering how this works. Thanks a lot Jane.