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 customee 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.

Dynamic Server 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 IBM Informix GLS User's Guide.