SUBSTRING (SQL)
Synopsis
SUBSTRING(string-expression,start[,length])
SUBSTRING(string-expression FROM start [FOR length])
{fn SUBSTRING(string-expression,start[,length])}
Arguments
Argument | Description |
---|---|
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. This field can be of any data type: a string (such as CHAR or VARCHAR), a numeric, or a data stream field of data type %Stream.GlobalCharacter or %Stream.GlobalBinary. |
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. |
Description
SUBSTRING takes data of any data type and returns a substring of that data as data type %String. The substring can, of course, be the full data value returned as a string.
The value of start controls the starting point of the substring:
-
If start is 1, the substring begins at the beginning of string-expression.
-
If start is greater than 1, the substring begins at that character position counted from the beginning of string-expression.
-
If start is less than 1, the substring begins at the beginning of string-expression, but the value of length is decremented by a corresponding amount. Thus, if start is 0, the value of length is diminished by 1; if start is –1, the value of length is diminished by 2.
The value of length controls the size of the substring:
-
If length is a positive value (1 or greater), the substring ends length number of characters to the right of the start position. (This effective length may be diminished if the start number is less than 1.)
-
If length is larger than the number of character remaining in the string, all characters to the right of the starting position through the end of string-expression are returned.
-
If length is zero, NULL is returned.
-
If length is a negative number, InterSystems IRIS issues an SQLCODE –140 error.
SUBSTRING can be used as an ODBC scalar function (with the curly brace syntax) or as an SQL general function.
Return Value
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, %Stream.GlobalCharacter), the SUBSTRING return value is %String.
If any SUBSTRING argument value is NULL, SUBSTRING returns NULL.
Stream Data
Unlike most SQL string functions, SUBSTRING can be used with stream data. The string-expression can be a field of data type %Stream.GlobalCharacter or %Stream.GlobalBinary. SUBSTRING returns the extracted subset of the stream data as %String data type. If start is 1 and length is omitted, SUBSTRING returns the full stream data value as a %String.
SUBSTRING can therefore be used to supply character stream data as a string to other SQL string functions. The following example uses SUBSTRING to allow CHARINDEX to search the first 1000 characters of a %Stream.GlobalCharacter field containing DNA nucleotide sequences for the first occurrence of the substring TTAGGG and returns that position as an integer:
SELECT CHARINDEX('TTAGGG',SUBSTRING(DNASeq,1,1000)) FROM Sample.DNASequences
SUBSTRING vs. SUBSTR
-
SUBSTRING extracts a substring from a start position counted from the beginning of a string-expression. SUBSTR can extract a substring from either the beginning or the end of a string.
-
SUBSTRING can be used with stream data; SUBSTR cannot be used with stream data.
Examples
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:
SELECT Name,SUBSTRING(Name FROM 1 FOR 4) AS FirstFour
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:
SELECT {fn SUBSTRING( 'ABCDEFG',-2,4 )} AS SubText