Partial characters in an ORDER BY clause

Partial characters might also create a problem when you specify column substrings in an ORDER BY clause of a SELECT statement.

The syntax for specifying column substrings in the ORDER BY clause is as follows. 

1  %SELECT statement(1)  ORDER BY column?  [ first , last ]
Notes:
  • 1 See HCL OneDB™ Guide to SQL: Syntax.
Element
Description
column
Name of a column in the specified table or view.
first. last
Positions of the first and last byte of the substring.

The query results are sorted by the values contained in this column.

In hierarchical queries, you can optionally specify the ORDER SIBLINGS BY clause, which uses similar syntax to sort the rows returned by the CONNECT BY clause for every level of the data hierarchy.

If the locale supports a multibyte code set whose characters are all of the same length, you can use column substrings in an ORDER BY clause. The more typical scenario, however, is that your multibyte code set contains characters with varying lengths. In this case, you might not find it useful to specify column substrings in the ORDER BY clause.

For example, suppose that you want to retrieve all the rows of the multi_data table, and sort the results according to a substring defined as the fourth through sixth characters of the multi_chars column, by using this query: 
SELECT * FROM multi_data ORDER BY multi_chars[7,12]

If the locale supports a multibyte code set whose characters are all 2 bytes in length, you know that the fourth character in the column begins in byte position 7, and the sixth character in the column ends in byte position 12. The preceding SELECT statement does not generate partial characters.

If the multibyte code set contains a mixture of single-byte characters, 2-byte characters, and 3-byte characters, however, the substring multi_chars[7,12] might create partial characters. In this case, you might get unexpected results when you specify a column substring in the ORDER BY clause.

For information about the collation of different types of character data in the ORDER BY clause, see ids_gug_137.html#ids_gug_137. For the complete syntax and usage of the ORDER BY clause (and of the ORDER SIBLINGS BY clause of hierarchical queries that include the CONNECT BY clause), see the SELECT statement in the HCL OneDB Guide to SQL: Syntax.
Tip: A partial character might also be generated when a SQL API copies multibyte data from one buffer to another. For more information, see Generate non-ASCII file names.