How To Find Max() Value Of Character Column?

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 whi

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:

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

SELECTMAX(INT(FLD1)) 
WHERE FLD1 <>' 'ANDTRANSLATE(FLD1, '0123456789', '0123456789') = FLD1

SQL Reference: TRANSLATE

---

And the reasonable explanation:

SQL Reference: MAX

Solution 4:

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.