如何找到字符列的 MAX() 值? [英] 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'

等等

现在，我们有功能请求，我们必须知道 MAX(code) 才能为用户提供下一个可能的值，例如下一个值上方的表单是 '100'.

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'.

我们试图对此进行试验，但我们仍然找不到任何合理的解释 DB2 引擎如何计算它

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

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

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

MAX('099', '99', 'www') 是 '99' 等等.

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

任何帮助或建议将不胜感激！

Any help or suggestion would be much appreciated!

推荐答案

你已经有了获取最大数值的答案，但是关于'www','099','99'的另一部分答案.

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

AS/400 使用 EBCDIC 来存储值，这在几个方面与 ASCII 不同，最重要的是您的目的是 Alpha 字符出现在数字之前，这与 Ascii 相反.

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.

所以在您的 Max() 上，您的 3 个字符串将被排序并使用最高的 EBCDIC 值

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

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

正如您所见，您的 '99' 字符串实际上是 '99'，因此它高于前导零的字符串.

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

这篇关于如何找到字符列的 MAX() 值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！