Products Downloads


French version


 

 

Mobile DBMS version: this function is not permitted with DB2.

 

Syntax

SUBSTRING(String,Start,Length)

 

Description

The SUBSTRING SQL scalar function is used to obtain a substring of a string.

 

The result is a character string.

If any argument of the SUBSTRING function can be null, the result can be null; if any argument is null, the result is the null value.

 

string

Denotes an argument that specifies the string from which the result is derived.

A substring of string is zero or more contiguous bytes of string.
 

start

Denotes an argument that specifies the position of the first character (or byte) of the result. It must be a positive binary integer that is not greater than the length attribute of string. (The length attribute of a varying-length string is its maximum length.)
 

length

Denotes an optional argument that specifies the length of the result. If specified, length must be a binary integer in the range 0 to n, where n is the length attribute of string - start + 1. It must not, however, be the integer constant 0.

 

If string is a fixed-length string, omission of length is an implicit specification of LENGTH(string) - start + 1, which is the number of characters (or bytes) from the start character (or byte) to the last character (or byte) of string.

 

If string is a varying-length string, omission of length is an implicit specification of zero or LENGTH(string) - start + 1, whichever is greater. If the resulting length is zero, the result is the empty string.

 

Example

First case:

Assume the column NAME (ALPHA(50)) has a value of 'KATIE AUSTIN'.

 

SUBSTRING(NAME, 7)

 

Returns the value 'AUSTIN'

 

SUBSTRING(NAME, 7, 1)

 

Returns the value 'A'.

 

Second case:

Select all rows from the PROJECT table for which the project name (PROJNAME) starts with the word 'OPERATION'.

 

CURSOR C1 PROJECT

*COND(SUBSTRING(PROJNAME,1,10) = 'OPERATION ')

 

The space at the end of the constant is necessary to preclude initial words such as 'OPERATIONS'.

 

List of SQL scalar functions by topic

 

↑ Top of page


  • Aucune étiquette