SUBSTRING function [String]

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

string-expression The string from which a substring is to be returned.
start The start position of the substring to return, in characters.
length The length of the substring to return, in characters. If length is specified, the substring is restricted to that length.

LONG BINARY
LONG VARCHAR
LONG NVARCHAR

The behavior of this function depends on the setting of the ansi_substring database option. When the ansi_substring option is set to On (the default), the behavior of the SUBSTRING function corresponds to ANSI/ISO SQL/2008 behavior. The behavior is as follows:

ansi_substring option setting	start value	length value
On	The first character in the string is at position 1. A negative or zero start offset is treated as if the string were padded on the left with non-characters.	A positive length specifies that the substring ends length characters to the right of the starting position. A negative length returns an error.
Off	The first character in the string is at position 1. A negative starting position specifies a number of characters from the end of the string instead of the beginning. If start is zero and length is non-negative, a start value of 1 is used. If start is zero and length is negative, a start value of -1 is used.	A positive length specifies that the substring ends length characters to the right of the starting position. A negative length returns at most length characters up to, and including, the starting position, from the left of the starting position.

ansi_substring option setting

start value

length value

On

The first character in the string is at position 1. A negative or zero start offset is treated as if the string were padded on the left with non-characters.

A positive length specifies that the substring ends length characters to the right of the starting position.

A negative length returns an error.

Off

The first character in the string is at position 1. A negative starting position specifies a number of characters from the end of the string instead of the beginning.

If start is zero and length is non-negative, a start value of 1 is used. If start is zero and length is negative, a start value of -1 is used.

A positive length specifies that the substring ends length characters to the right of the starting position.

A negative length returns at most length characters up to, and including, the starting position, from the left of the starting position.

If string-expression is of binary data type, the SUBSTRING function behaves as BYTE_SUBSTR.

To obtain characters at the end of a string, use the RIGHT function.

This function supports NCHAR inputs and/or outputs. Whenever possible, if the input string uses character-length semantics, the return value is described in character-length semantics.

SQL/2008 SUBSTRING is a core feature of the SQL/2008 standard. The standard's implementation differs slightly from the SQL Anywhere implementation: in the standard, SUBSTRING is defined with three parameters using the keywords FROM and FOR, neither of which are required by SQL Anywhere.

The following table shows the values returned by the SUBSTRING function.

Example	Result
SUBSTRING( 'front yard', 1, 4 )	fron
SUBSTRING( 'back yard', 6, 4 )	yard
SUBSTR( 'abcdefgh', 0, -2 )	Returns an error if ansi_substring is On
SUBSTR( 'abcdefgh', -2, 2 )	Returns an empty string if ansi_substring is On

SUBSTRING function [String]

Syntax

Parameters

Returns

Remarks

See also

Standards and compatibility

Example