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

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

p> 

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

p> 

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

p> 

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

p> 

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

p> 

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

p> 

p>LENGTH scalar function

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

p>                          '-,--+-CODEUNITS16-+-'      

p>                               +-CODEUNITS32-+        

p>                               '-OCTETS------'        

p>expression

p> 

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

p> 

p>CODEUNITS16, CODEUNITS32, or OCTETS

p> 

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

p> 

p>最后过滤语句为

p> 

p>1

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

p> 

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

p> 

p>TRANSLATE scalar function

p>character string expression:

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

p> 

p>>--+-----------------------------------------------------------+-->

p>   |                                       .-,--' '----------. |   

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

p>                                           '-,--pad-char-exp-'     

p> 

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

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

p> 

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

p> 

p>char-string-exp or graphic-string-exp

p> 

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

p> 

p>to-string-exp

p> 

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

p> 

p>from-string-exp

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

p> 

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

p> 

p>pad-char-exp

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

p> 

p>具体实现语句如下：

p>1

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

p>这条sql语句简单说明，当ITEM字段的中包含0~9中的任一数字时，就替换为空字符，当全部为数字时，就全部替换为空字符了，通过trim()函数去空格然后length函数一测量，连渣都不剩了.为0.此时就可以判定为全部是数字字符了。