SUBSTRING Function [String]

Syntax

{ SUBSTRING | SUBSTR } ( string-expression, start [ , length ] )

Parameters

Parameter	Description
string-expression	The string from which a substring is to be returned.
start	The start position of the substring to return, in characters. A negative starting position specifies a number of characters from the end of the string instead of the beginning. The first character in the string is at position 1.
length	The length of the substring to return, in characters. A positive `length` specifies that the substring ends `length` characters to the right of the starting position, while a negative `length` specifies that the substring ends `length` characters to the left of the starting position.

Returns

LONG VARCHAR

LONG NVARCHAR

LONG BINARY

Note: The result data type is a LONG VARCHAR. If you use STRING in a SELECT INTO statement, you must have an Unstructured Data Analytics Option license or use CAST and set STRING to the correct data type and size.

Examples

The following statement returns “back”:

SELECT SUBSTRING ( 'back yard', 1 , 4 )
FROM iq_dummy

The following statement returns yard:

SELECT SUBSTR ( 'back yard', -1 , -4 )
FROM iq_dummy

The following statement returns 0x2233:

SELECT SUBSTR ( 0x112233445566, 2, 2 )
FROM iq_dummy

Usage

If length is specified, the substring is restricted to that length. If no length is specified, the remainder of the string is returned, starting at the start position.

Both start and length can be negative. Using appropriate combinations of negative and positive numbers, you can get a substring from either the beginning or end of the string.

If you are licensed to use the Unstructured Data Analytics functionality, you can use this function with large object data.

When the ansi_substring database option is set to ON (default), negative values are invalid.

See SUBSTRING Function in Unstructured Data Analytics.

Standards and Compatibility

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—SUBSTR is not supported by Adaptive Server Enterprise. Use SUBSTRING instead