Using the Substring Operator

You can use the substring operator on CHAR, VARCHAR, NCHAR, NVARCHAR, BYTE, and TEXT columns to define a column substring as the portion of the column that is specified by the expression.

After the identifier of a character column, when a pair of bracket ( [ ] ) symbols enclose a comma-separated pair of unsigned integers in which the first integer is greater than zero but not greater than the last integer, HCL OneDB™ interprets the brackets as the substring operator. The expression returns the first through last characters of the data value in the column, where first and last define a substring. For example, in the expression cat_advert [6,15], the returned value is the 6th through 15th characters of column cat_advert.

In the default locale, if the data value occupies at least 15 bytes, this expression evaluates to a substring that includes ten bytes of the column value, but in a multibyte locale this expression returns a string of ten consecutive logical characters whose storage length might exceed 10 bytes, beginning with the sixth logical character. For more information on the GLS aspects of column substrings, see the HCL OneDB GLS User's Guide.

In the following example, if a value in the lname column of the customer table is Greenburg, the following expression evaluates to burg:

lname[6,9]

A conditional expression can include a column expression that uses the substring operator ( [ first, last ] ), as in the following example:

SELECT lname FROM customer WHERE phone[5,7] = '356';

Here the quotation marks are required, to prevent the database server from applying a numeric filter to the digits in the criterion value.

See also the section String-Manipulation Functions, which describes two built-in SQL functions, SUBSTR( ) and SUBSTRING( ) that can specify a substring expression within an SQL statement.

Note: The database server can use substrings defined by the substring operator as index filters in queries. This is not the case, however, for substrings defined by SUBSTR( ) or SUBSTRING( ), nor for other built-in string manipulation functions.