Chapter 56 SQL function $EXTRACT

A string function that extracts characters from a string by position.

outline

$EXTRACT(string[,from[,to]])

parameter

• String - the target string from which to extract the substring.
• from - optional - the position of a single character in the target string, or the beginning of the range of characters to extract, including.
  Specify as a positive integer counting from 1.
• To - optional - the end position of the character range to extract (including).
  Specify as a positive integer counting from 1.

describe

$EXTRACT returns a substring at a specified position in a string.
The nature of the substring returned depends on the parameters used.

• $EXTRACT(string) extracts the first character in the string.
• $EXTRACT(string,from) extracts characters from the position specified by from.
  For example, if the variable var1 contains the string "ABCD", the following command extracts "B" (the second character):

SELECT $EXTRACT('ABCD',2) AS Extracted

2

• $EXTRACT(string,from,to) extracts the character range starting at the from position and ending at the to position.
  For example, the following command extracts the string "Alabama" (i.e. all characters from position 5 to position 11, inclusive) from the string "1234Alabama567":

SELECT $EXTRACT('1234Alabama567',5,11) AS Extracted

Alabama

This function returns data of type VARCHAR.

parameter

string

The string value can be a variable name, a numeric value, a string literal, or any valid expression.

from

The from value must be a positive integer (see note, however).
If it is a decimal, the decimal part is truncated and only the integer part is used.

If the from value is greater than the number of characters in the string, $EXTRACT returns an empty string.

If from is specified without the to parameter, the specified single character is extracted.

If used with the to parameter, it identifies the beginning of the range to extract and must be less than the value of to.
If from = to, $EXTRACT returns a single character at the specified position.
If from is greater than to, $EXTRACT returns an empty string.

to

The to parameter must be used with the from parameter.
It must be a positive integer.
If it is a decimal, the decimal part is truncated and only the integer part is used.

If the value of to is greater than or equal to the value of from, $EXTRACT returns the specified substring.
If to is greater than the length of the string, $EXTRACT returns a substring from the position to the end of the string.
If to is less than from, $EXTRACT returns an empty string.

Examples

The following example returns the fourth character "S" in a string:

SELECT $EXTRACT('THIS IS A TEST',4) AS Extracted

S

The following example returns the substring "this" consisting of the first to seventh characters.

SELECT $EXTRACT('THIS IS A TEST',1,7) AS Extracted

THIS IS

The following embedded SQL example extracts the second character ("B") from a and assigns this value to the variable y.

ClassMethod Extract()
{
	s a="ABCD"
	&sql(SELECT $EXTRACT(:a,2) INTO :y)
	if SQLCODE '= 0 {
		w !,"Error code ",SQLCODE }
	else {
		w !,"The extract returns ",y }
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).Extract()
 
The extract returns B

The following embedded SQL example shows that when the from value is "1", the single parameter format is equivalent to the two parameter format.
Both $EXTRACT functions return "H".

/// d ##class(PHA.TEST.SQLCommand).Extract1()
ClassMethod Extract1()
{
	s a="HELLO"
	&sql(SELECT $EXTRACT(:a),$EXTRACT(:a,1) INTO :b,:c)
	if SQLCODE'=0 {
		w !,"Error code ",SQLCODE }
	else {
		w !,"The one-arg form returns ",b
		w !,"The two-arg form returns ",c }
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).Extract1()
 
The one-arg form returns H
The two-arg form returns H

be careful

E X T R A C T And EXTRACT and Compare EXTRACT with pie and $LIST

$EXTRACT returns a substring by integer position from a string.
Both $PIECE and $LIST apply to specially formatted strings.

$PIECE returns a substring from a standard string using a delimiter.

$LIST returns a child LIST of elements in the encoding LIST by the integer position of the element (not a character).
$LIST cannot be used for ordinary strings and $EXTRACT cannot be used for encoded lists.

The $EXTRACT, $FIND, $LENGTH, and $PIECE functions operate on standard strings.
Various $LIST functions operate on encoded strings that are incompatible with standard strings.
The only exceptions are the single argument and two argument forms of the $LISTGET function and $LIST, which accept an encoded string as input but output a single element value as a standard string.

$EXTRACT and Unicode

The $EXTRACT function operates on characters instead of bytes.
Therefore, Unicode strings are processed in the same way as ASCII strings. The embedded SQL example shown below uses the Unicode character "pi" ($CHAR(960)):

ClassMethod Extract2()
{
	s a="QT PIE"
	s b=("QT "_$CHAR(960))
	&sql(SELECT 
	$EXTRACT(:a,-33,4),
	$EXTRACT(:a,4,4),
	$EXTRACT(:a,4,99),
	$EXTRACT(:b,-33,4),
	$EXTRACT(:b,4,4),
	$EXTRACT(:b,4,99)
	INTO :a1,:a2,:a3,:b1,:b2,:b3)
	if SQLCODE'=0 {
		w !,"Error code ",SQLCODE }
	else {
		w !,"ASCII form returns ",!,a1,!,a2,!,a3
		w !,"Unicode form returns ",!,b1,!,b2,!,b3 }
}

DHC-APP> d ##class(PHA.TEST.SQLCommand).Extract2()
 
ASCII form returns
QT P
P
PIE
Unicode form returns
QT π
π
π

Null and invalid parameters

• When the string is a null string, a null string is returned.
• When from is a number greater than the length of the string, an empty string is returned.
• When from is zero or negative and no to is specified, an empty string is returned.
• When to is zero, negative, or a number smaller than from, an empty string is returned.
• When to is a valid value, from can be zero or negative.
  $EXTRACT treats these values as 1.

Invalid parameter values do not generate SQLCODE errors.

In the following example, the negative number of the from value is calculated as 1;
$EXTRACT returns the substring "this" consisting of the first to seventh characters.

SELECT $EXTRACT('THIS IS A TEST',-7,7)

THIS IS

In the following embedded SQL example, all $EXTRACT function calls return an empty string:

ClassMethod Extract3()
{
	s a="THIS IS A TEST"
	s b=""
	&sql(SELECT 
	$EXTRACT(:a,33),
	$EXTRACT(:a,-7),
	$EXTRACT(:a,3,2),
	$EXTRACT(:a,-7,0),
	$EXTRACT(:a,-7,-10),
	$EXTRACT(:b,-33,4),
	$EXTRACT(:b,4,4),
	$EXTRACT(:b,4,99),
	$EXTRACT(NULL,-33,4),
	$EXTRACT(NULL,4,4),
	$EXTRACT(NULL,4,99)
	INTO :a1,:a2,:a3,:a4,:a5,:b1,:b2,:b3,:c1,:c2,:c3)
	if SQLCODE'=0 {
		w !,"Error code ",SQLCODE }
	else {
		w !,"FROM too big: ",a1
		w !,"FROM negative, no TO: ",a2
		w !,"TO smaller than FROM: ",a3
		w !,"TO not a positive integer: ",a4,a5
		w !,"LIST is null string: ",b1,b2,b3,c1,c2,c3 }
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).Extract3()
 
FROM too big:
FROM negative, no TO:
TO smaller than FROM:
TO not a positive integer:
LIST is null string: