XML Guide

10.5.6
- 10.5.7
- 10.5
- 10.4.0

Back Next

substring

Returns a portion of a string starting at a specified position. Substring includes blanks as characters in the string.

Syntax

substring ( string, start [ ,length ] )

The following table describes arguments for this function:

Argument	Description
string	String datatype. The string to search.
start	Integer datatype. Passes the position in the string to start counting. If the start position is a positive number, substring locates the start position by counting from the beginning of the string. The first character is one. If the start position is a negative number, substring locates the start position by counting from the end of the string.
length	Integer datatype. Must be greater than zero. Passes the number of characters to return in a string. If you omit the length argument, substring returns all of the characters from the start position to the end of the string.

Return Value

String.

When the string contains a numeric value, the function returns a character string.

If you pass a negative integer or zero, the function returns an empty string.

NULL if a value passed to the function is NULL.

Examples

The following expression returns the area code in PHONE:

substring( PHONE, 1, 3 )

PHONE	RETURN VALUE
809-555-3915	809
NULL	NULL

The following expression returns the PHONE without the area code:

substring ( phone, 5, 8 )

The following table includes example arguments and return values without the area codes:

PHONE	RETURN VALUE
808-555-0269	555-0269
NULL	NULL

You can pass a negative start value to start from the right side of the string. The expression reads the source string from left to right for the length
argument:

substring ( PHONE, -8, 3 )

The following table includes example arguments and return values when the expression reads the source string from left to right:

PHONE	RETURN VALUE
808-555-0269	555
809-555-3915	555
357-687-6708	687
NULL	NULL

When the length
argument is longer than the string, substring returns all the characters from the start position to the end of the string. For example:

substring ( 'abcd', 2, 8 )

returns ‘bcd.’

substring ( 'abcd', -2, 8 ) 
returns ‘cd.’

Rename Saved Search

Table of Contents

XML Guide

XML Guide

substring

substring

Syntax

Return Value

Examples