Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » UltraLite - Database Management and Reference » UltraLite SQL Function Reference » Alphabetical list of functions

STUFF function [String] Next Page

SUBSTRING function [String]

Returns a substring of a 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.


In UltraLite, the database does not have an ansi_substring option. Nonetheless, the SUBSTR function behaves as if ansi_substring is set to on by default. In other words, the function's behavior corresponds to ANSI/ISO SQL/2003 behavior:

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

It is recommended that you avoid using non-positive start offsets or negative lengths with the SUBSTRING function. Where possible, use the LEFT or RIGHT functions instead.

Whenever possible, if the input string uses character length semantics the return value is described in terms of character length semantics.

See also
Standards and compatibility

The following table shows the values returned by the SUBSTRING function when used in a SELECT statement, with the ansi_substring option set to On and Off.

ExampleResult with ansi_substring set to OnResult with ansi_substring set to Off
SUBSTRING( 'front yard', 1, 4 )fronfron
SUBSTRING( 'back yard', 6, 4 )yardyard
SUBSTR( 'abcdefgh', 0, -2 )Returns an errorgh
SUBSTR( 'abcdefgh', -2, 2 )Returns an empty stringgh
SUBSTR( 'abcdefgh', 2, -2 )Returns an errorab
SUBSTR( 'abcdefgh', 2, -4 )Returns an errorab
SUBSTR( 'abcdefgh', 2, -1 )Returns an errorb