In this example, the session collation is ASCII when submitting the CREATE TABLE statement, and the pad character is <space>. The example defines two partitions (numbered 1 and 2) based on the value of a:
- The value of a is between 'a ' (a followed by 9 spaces) and 'b '.
- The value of a is between 'b ' and 'c '.
CREATE SET TABLE t2 (a VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC, b INTEGER) PRIMARY INDEX (a) PARTITION BY CASE_N(a BETWEEN 'a' AND 'b', a BETWEEN 'b' AND 'c');
The following INSERT statement inserts a character string consisting of a single <tab> character between the 'b' and '1'.
INSERT t2 ('b 1', 1);
The following INSERT statement inserts a character string consisting of a single <space> character between the 'b' and '1'.
INSERT t2 ('b 1', 2);
The following SELECT statement shows the result of the INSERT statements. Since the <tab> character has a lower code point than the <space> character, the first string inserted maps to partition 1.
SELECT PARTITION, a, b FROM t2 ORDER BY 1;
Result:
*** Query completed. 2 rows found. 3 columns returned. *** Total elapsed time was 1 second. PARTITION a b ----------- ------ ----- 1 b 1 1 (string contains single <tab> character) 2 b 1 2 (string contains single <space> character)