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

时间 2019-12-07 标签 db2 db 2 判断汉字长度对数字字符进行判断

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

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

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

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

经过查看DB2文档，发现length函数是能够经过编码格式不一样来实现输出不一样的长度，具体以下：函数

LENGTH scalar function

 >>-LENGTH--(--expression--+--------------------+--)------------><
                          '-,--+-CODEUNITS16-+-'      
                               +-CODEUNITS32-+        
                               '-OCTETS------'

expression ui

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 lua

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

最后过滤语句为 scala

( 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 evaluating 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 evaluating 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

Specifies a string of characters which, if found in char-string-exp, will be converted to the corresponding character in to-string-exp.

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 evaluating the function.If from-string-exp contains duplicate characters, the first one found will be used, and the duplicates will be ignored. If to-string-exp is longer than from-string-exp, the surplus characters will be ignored. If to-string-exp is specified, from-string-exp must also be specified.

pad-char-exp

Specifies a single character that will be used to pad to-string-exp if to-string-exp is shorter than from-string-exp. 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 evaluating the function. The value must have a length attribute of zero or one. If a zero-length string is specified, characters in the from-string-exp with no corresponding character in the to-string-exp are removed from char-string-exp or graphic-string-exp. If a value is not specified a single-byte blank character is assumed.

具体实现语句以下：

length(trim(translate(r.ITEM,'','0123456789')))=0

这条sql语句简单说明，当ITEM字段的中包含0~9中的任一数字时，就替换为空字符，当所有为数字时，就所有替换为空字符了，经过trim()函数去空格而后length函数一测量，连渣都不剩了.为0.此时就能够断定为所有是数字字符了。