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

A string function that returns a substring from a larger character string.

SUBSTRING(string-expression FROM start [FOR length])

{fn SUBSTRING(string-expression,start[,length])}
string-expression The string expression from which the substring is to be derived. An expression, which can be the name of a column, a string literal, or the result of another scalar function. The underlying data type can be a character type (such as CHAR or VARCHAR), a numeric, or a data stream.
start An integer that specifies the position in string-expression to begin the substring. The first character in string-expression is at position 1. If the start position is higher than the length of the string, SUBSTRING returns an empty string (''). If the start position is lower than 1 (zero, or a negative number) the substring begins at position 1, but the length of the substring is reduced by the start position.
length Optional — An integer that specifies the length of the substring to return. If length is not specified, the default is to return the rest of the string.
The value of start controls the starting point of the substring:
The value of length controls the size of the substring:
Floating-point numbers passed as arguments to SUBSTRING are converted to integers by truncating the fractional portion.
SUBSTRING extracts a substring from the beginning of a string. SUBSTR can extract a substring from either the beginning or the end of a string. Note that these two SQL functions handle argument values differently. SUBSTRING can be used with character stream data; SUBSTR cannot be used with stream data.
SUBSTRING can be used as an ODBC scalar function (with the curly brace syntax) or as an SQL general function.
Return Value
If any SUBSTRING argument value is NULL, SUBSTRING returns NULL.
If string-expression is any %String data type, the SUBSTRING return value is the same data type as the string-expression data type. This allows SUBSTRING to handle user-defined string data types with special encoding.
If string-expression is not a %String data type (for example, %Float), the SUBSTRING return value is %String.
This example returns the string “forward”:
SELECT {fn SUBSTRING( 'forward pass',1,7 )} AS SubText
This example returns the string “pass”:
SELECT {fn SUBSTRING( 'forward pass',9,4 )} AS SubText
The following example returns the first four characters of each name:
SELECT Name,SUBSTRING(Name,1,4) AS FirstFour
FROM Sample.Person
The following example demonstrates another syntactical form of SUBSTRING. This example is functionally the same as the previous example:
FROM Sample.Person
The following example shows how the length is reduced by a start value of less than 1. (A start value of 0 reduces length by 1, a start value of -1 reduces length by 2, and so forth.) In this case, length is reduced by 3, so only one character (“A”) is returned:
See Also

Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA