You can specify character expressions (CHAR, VARCHAR, GRAPHIC or VARGRAPHIC) as the test_expression and/or the range boundaries in a RANGE_N function. The following usage rules apply:
- A RANGE_N partitioning expression can use the UPPERCASE qualifier and the following functions: LOWER, UPPER, TRANSLATE, TRIM, VARGRAPHIC, INDEX, MINDEX, POSITION, TRANSLATE_CHK, CHAR2HEXINT.
- If test_expression is a character data type, you cannot specify the EACH phrase.
- Any string literal referenced within a RANGE_N expression must be less than 31,000 bytes.
- If test_expression is a character data type, and the length of any of the range boundaries (minus trailing pad characters) is greater than the length of test_expression, an error is returned.
- For character RANGE_N partitioning, the increasing order of ranges is determined by the session collation and case specificity of the test_expression. If the test_expression is a combination of NOT CASESPECIFIC expressions and a literal with no case specific qualifier (CASESPECIFIC, NOT CASESPECIFIC), the case specificity will be case specific in ANSI mode sessions and not case specific in Teradata mode sessions.All character string comparisons involving graphic data are case specific.
- An error is returned if any of the specified ranges are defined with null boundaries, are not increasing, or overlap. For character test values, increasing order is determined by the session collation and case specificity of the test_expression.
- In character comparison operations (=, <, >, <=, >=, <>, BETWEEN, LIKE), if a string literal is shorter than the column data to which it is compared, the string literal is treated as if it is padded with a pad character specific to the character set (for example, a <space> character). Therefore, if a character test_expression is defined with a longer length than a character range boundary, comparison of the test _expression to that range boundary will behave as if the range boundary is padded with pad characters.
Note that the pad character might not collate to the lowest code point in the collation. For a range boundary of length n, if the test_expression precisely matches that range boundary for the first n characters, but contains a character that collates less than the pad character at position n +1, then the test_expression will collate less than the range boundary. See Examples.