Returns a substring of a string.
Syntax{ SUBSTRING | SUBSTR } ( string-expression, start
[, length ] ) Parametersstring-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.
Returns RemarksIn UltraLite, the database does not have an ansi_substring option, but the SUBSTR function behaves as if ansi_substring is set to on by default. The function's behavior corresponds to ANSI/ISO SQL/2008 behavior:
Start value 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.
Length value A positive length specifies that the substring ends length characters to the right of the starting position.
A negative length returns an error.
A length of zero returns an empty string.
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.
Whenever possible, if the input string uses character-length semantics, the return value is described in character-length semantics.
See also Standards and compatibilitySQL/2008 SUBSTRING is a core feature of the SQL/2008 standard. The standard's implementation differs slightly from the UltraLite implementation: in the standard, SUBSTRING is defined with three parameters using the keywords FROM and FOR, neither of which are required by UltraLite.
ExampleThe 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 |
| SUBSTR( 'abcdefgh', -2, 2 ) | Returns an empty string |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |