DB2 determines the length of Chinese characters and numbers

DB2 determines the length of Chinese characters and determines the number of characters. There is a module that requires writing validation rules, and there is a requirement that the XXX described in this rule must be greater than three or two Chinese characters, filter out all nonconformities. We all know that we use the length () function for determining the length of characters, but what about Chinese characters? This is a problem. The length function is used to determine that the length of a Chinese character is 3, that is, a Chinese character is equal to 3 bytes. (BTW: The project uses DB2 to set the encoding format is UTF-8) by viewing the DB2 document, we found that the length function can achieve different output length through different encoding formats, as shown below: LENGTH scalar function>-LENGTH -- (-- expression -- + -------------------- + --) ------------> <'-, -- +-CODEUNITS16-+-'+-CODEUNITS32-+'-ETS ------ '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. CO DEUNITS16, 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. the final filter statement is 1 (length (r. ITEM, CODEUNITS32) <2 or length (r. ITEM) <3) for the determination of numeric characters, the function used is translate () T RANSLATE scalar functioncharacter string expression:>-TRANSLATE -- (-- char-string-exp ----------------------------------> -- + accept + --> |. -,--''----------. | '-, -- 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 Specifi Es 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 con Verted. 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-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 tables is not mapped, because code page 850 does not include tables. 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-expSpecifies 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-expSpecifies 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 t He 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. the specific implementation statement is as follows: 1 length (trim (translate (r. ITEM, '', '000000') = 0 this SQL statement is simple description, when the ITEM field contains 0 ~ When any number in 9 is replaced with null characters. When all are digits, all are replaced with null characters. Use the trim () function to remove spaces and then the length function is used for measurement, no slag left. 0. it can be determined that all are numeric characters.