InterSystems SQL Reference
SUBSTR
|
|
A string function that returns a substring that is derived from a specified string expression.
Synopsis
SUBSTR(string-expression,start[,length])
Because
start can be negative, you can obtain a substring from either the beginning or end of the original string.
Floating-point numbers passed as arguments to
SUBSTR are converted to integers by truncating the fractional portion.
-
If
start is 0, 0, or 1, the returned substring begins with the first character of the string.
-
If
start is a negative number the returned substring begins that number of characters from the end of the string, with -1 representing the last character of the string. If the negative number is so large that its value counted backwards from the end of the string would position before the beginning of the string, the returned substring begins with the first character of the string.
-
If
start is past the end of the string, NULL is returned.
-
If
length larger than the remaining characters in the string, the substring from
start to the end of the string is returned.
-
If
length is less that 1, NULL is returned.
-
SUBSTR is supported for Oracle compatibility.
The following example returns the substring CDEFG because it specifies that the substring begin at the third character (C) and continue to the end of the string:
SELECT SUBSTR('ABCDEFG',3) AS Sub
The following example returns the substring CDEF because it specifies that the substring begin at the third character (C) and continue for four characters (until F):
SELECT SUBSTR('ABCDEFG',3,4) AS Sub
The following example returns the substring CDEF because it specifies that InterSystems IRIS should first count five characters backwards from the end of the original string, and then return the next four characters:
SELECT SUBSTR('ABCDEFG',-5,4) AS Sub