- 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.
- The case sensitivity of column references and literals is determined based on the session default, or an explicit CAST, or a specification in the CREATE TABLE statement when the table was created. The column can be explicitly assigned to be CASESPECIFIC or NOT CASESPECIFIC, and constant expressions can be CAST with these qualifiers.
If not explicitly specified, the default of NOT CASESPECIFIC is used if Teradata session transaction semantics are in effect. If ANSI session transaction semantics are in effect, the default is CASESPECIFIC.
For example, if a conditional expression is a combination of NOT CASESPECIFIC expressions and a constant with no case sensitivity qualifier (CASESPECIFIC, NOT CASESPECIFIC), the case sensitivity will be case sensitive in ANSI mode sessions and case blind in Teradata mode sessions.
All character string comparisons involving graphic data are case sensitive.
- The case sensitivity of column references and literals is determined based on the session default, or an explicit CAST, or a specification in the CREATE TABLE statement when the table was created. The column can be explicitly assigned to be CASESPECIFIC or NOT CASESPECIFIC, and constant expressions can be CAST with these qualifiers.
- 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.
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: