Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
SELECT statementUse the SELECT statement to retrieve values from a database or from an SPL or collection variable. A SELECT operation is called a query.
ORDER BY ClauseThe ORDER BY clause sorts query results by specified columns or expressions.
Ordering by a Substring

Ordering by a Substring

You can order by a substring instead of by the entire length of a character, BYTE, or TEXT column, or of an expression returning a character string. The database server uses the substring to sort the result set. Define the substring by specifying integer subscripts (the first and last parameters), representing the starting and ending byte positions of the substring within the column value.

The following SELECT statement queries the customer table and specifies a column substring in the ORDER BY column. This instructs the database server to sort the query results by the portion of the lname column contained in the sixth through ninth bytes of the column value.

SELECT * from customer ORDER BY lname[6,9];

Assume that the value of lname in one row of the customer table is Greenburg. Because of the column substring in the ORDER BY clause, the database server determines the sort position of this row by using the value burg, rather than the complete column value Greenburg.

When ordering by an expression, you can specify substrings only for expressions that return a character data type. If you specify a column substring in the ORDER BY clause, the column must have one of the following data types: BYTE, CHAR, NCHAR, NVARCHAR, TEXT, or VARCHAR.

HCL OneDB™ can also support LVARCHAR column substrings in the ORDER BY clause, if the column is in a database of the local database server.

For information on the GLS aspects of using column substrings in the ORDER BY clause, see the HCL OneDB GLS User's Guide.