ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

SUBSTRING function [String]

Function 

Returns a substring of a string.

Syntax 

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

Parameters 

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.

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 string-expression is of binary data type, the SUBSTRING function behaves as BYTE_SUBSTR.

Standards and compatibility 

• SQL/92    Entry-level feature.

• SQL/99    Core feature.

• Sybase    SUBSTRING is compatible with Adaptive Server Enterprise. SUBSTR is not supported by Adaptive Server Enterprise.

See also 

BYTE_SUBSTR function [String]

Example 

The following statement returns back :

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

The following statement returns yard :

SELECT SUBSTRING( 'back yard', -1 , -4 )