Caché SQL Reference
SUBSTR
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A string function that returns a substring that is derived from a specified string expression.
Synopsis
SUBSTR(string-expression,start[,length])
Arguments
string-expression The string expression from which the substring is to be derived. The expression can be the name of a column, a string literal, or the result of another scalar function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR).
start An integer that specifies where in string-expression the substring will begin. A positive starting position specifies the number of characters from the beginning of the string. The first character in string-expression1 is at position 1. A negative starting position specifies the number of characters from the end of the string. If start is 0 (zero), it is treated as 1.
length Optional — A positive integer that specifies the length of the substring to return. This value specifies that the substring ends length characters to the right of the starting position. If omitted, substring goes from start to the end of string-expression. If length is 0 or a negative number, Caché returns NULL.
Description
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.
SUBSTR cannot be used with stream data. If string-expression is a stream field, SUBSTR generates an SQLCODE -37. Use SUBSTRING to extract a substring from stream data.
SUBSTR is supported for Oracle compatibility.
Examples
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 Caché 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
 
See Also