DB2判断汉字长度、对数字字符进行判断

项目有个模块需要写校验规则，而规则中有个需求是这么描述的

XXX要求大于三个字符或两个汉字，把不满足的都过滤出来。

对于字符判断长度的想必大家都知道用length()函数，但是对于汉字呢？这是个问题。

通过用length函数测出一个汉字的长度是3，即一个汉字等于3个字节长度。（BTW:项目用的DB2设置的编码格式是UTF-8）

通过查看DB2文档，发现length函数是可以通过编码格式不同来实现输出不同的长度，具体如下：

LENGTH scalar function

>>-LENGTH--(--expression--+--------------------+--)------------><

'-,--+-CODEUNITS16-+-'

+-CODEUNITS32-+

'-OCTETS------'

expression

An expression that returns a value that is a built-in data type. If expression can be null, the result can be null; if expression is null, the result is the null value.

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the string unit of the result. CODEUNITS16 specifies that the result is to be expressed in 16-bit UTF-16 code units. CODEUNITS32 specifies that the result is to be expressed in 32-bit UTF-32 code units. OCTETS specifies that the result is to be expressed in bytes.

最后过滤语句为

1

( length(r.ITEM, CODEUNITS32)<2 or length(r.ITEM)<3)

对于数字字符的判断，用到的函数是translate()

TRANSLATE scalar function

character string expression:

>>-TRANSLATE--(--char-string-exp-------------------------------->

>--+-----------------------------------------------------------+-->

| .-,--' '----------. |

'-,--to-string-exp--,--from-string-exp--+-----------------+-'

'-,--pad-char-exp-'

>--)-----------------------------------------------------------><

The TRANSLATE function returns a value in which one or more characters in a string expression might have been converted to other characters.

The function converts all the characters in char-string-exp or graphic-string-exp that also occur in from-string-exp to the corresponding characters in to-string-exp or, if no corresponding characters exist, to the pad character specified by pad-char-exp.

char-string-exp or graphic-string-exp

Specifies a string that is to be converted. The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before eva luating the function.

to-string-exp

Specifies a string of characters to which certain characters in char-string-exp will be converted.The expression must return a value that is a built-in CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, numeric, or datetime data type. If the value is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type, it is implicitly cast to VARCHAR before eva luating the function.If a value for to-string-exp is not specified, and the data type is not graphic, all characters in char-string-exp will be in monocase; that is, the characters a-z will be converted to the characters A-Z, and other characters will be converted to their uppercase equivalents, if they exist. For example, in code page 850, é maps to , but is not mapped, because code page 850 does not include . If the code point length of the result character is not the same as the code point length of the source character, the source character is not converted.

from-string-exp