SUBSTRB function
Returns a substring of a string, beginning at a specified position in the string.
| Element | Description |
|---|---|
| length | An expression that specifies the length of the
result in bytes. If specified, the expression must return a value
that is a built-in numeric, CHAR, or VARCHAR data type. If the value
is not of type INTEGER, it is implicitly cast to INTEGER before the
function is evaluated. If the value of length is greater than the number of bytes from the starting position to the end of the string, the length of the result is equal to is the length of the first argument minus the starting position, plus one. If the value of length is less than or equal to zero, the result of SUBSTRB is a NULL string. The default for length is the number of bytes from the position specified by starting_position to the last byte of the string. When length is specified,
the length of the result string is truncated to the value of length.
In the following example, where my_string is a 10-byte string,
the result string is limited to 5 bytes: substrB(my_string, 3, 5)If, in the preceding example, my_string is a 4-byte string and the starting position is the third byte, a 2-byte string is returned. If length is
not specified, the length of the result is the length of source_string beginning
from the starting_position. In the following example, where my_string is
a 10-byte string, an 8-byte string is returned: substrB(my_string, 3) |
| source_string | An expression that specifies the string from which the result is derived. The expression must return a value that is a built-in string, numeric, or datetime data type. If the value is not a string data type, it is implicitly cast to NVARCHAR before the function is evaluated. A NULL value is returned for a zero length result. |
| starting_position | An expression that specifies the starting position
in string of the beginning of the result substring. The expression
must return a value that is a built-in numeric, CHAR, or VARCHAR data
type. If the value is not of type INTEGER, it is implicitly cast to
an INTEGER before the function is evaluated. If starting_position is positive, then the starting position is calculated from the beginning of the string. If starting_position is greater than the length of string, then a null string is returned. If starting_position is negative, then the starting position is calculated from the end of the string and by counting backwards the number of bytes. If the absolute value of starting_position is greater than the length of source_string, then a null string is returned. If starting_position is 0, then a starting position of 1 is used. Note: All units of length and starting_position are
expressed in terms of bytes, even for strings encoded in multibyte
code sets. SUBSTR uses the logical character size for multibyte strings.
For example, if starting_position is 2 in
the legacy SUBSTR and the first character of a multibyte string requires 3 bytes
of storage, the 2 represents the fourth byte in the
string. In SUBSTRB, the 2 represents the second byte
in the string. |
If source_string is a CHAR or VARCHAR data type, the result of the function is a VARCHAR data type. HCL OneDB™ does not support multiple code pages; instead, HCL OneDB JDBC or ODBC translates the code page to the database.
If any argument is null, the result is the null value.
In dynamic SQL, source_string, starting_position, and length can be represented by a host variable. If a host variable is used for source_string, the data type of the operand is VARCHAR, and the operand can be nullable.
Though not explicitly stated in the result definitions above, the semantics imply that if source_string is a multi-byte character string, the result might contain fragments of multi-byte characters, depending on the values of starting_position and length. For example, the result could possibly begin with the second byte of a multi-byte character, or end with the first byte of a multi-byte character. The SUBSTRB function detects these partial characters and replaces each byte of an incomplete character with a single blank character. SUBSTRB returns a fixed number of bytes; with SUBSTR, the number of returned varies according to the multibyte string.