SqlExpression SUBSTRING

From Eigenpedia

Contents 1 Syntax 2 Purpose 3 Input 4 Output 5 Example

Syntax

SUBSTRING( string, start_position [, length] )

SUBSTRING( string FROM start_position [ FOR length] )

Purpose

Extracts a substring from a string

Input

• string [VARCHAR/CHAR] - the string from which the substring will be extracted.
• start_position [INTEGER] - the position to begin extracting the substring. The beginning position of a string is 1.

• length [INTEGER] - Length of the substring to return. This is an optional parameter which defaults to the remaining length of the input.

Output

• [VARCHAR] - the extracted substring.
  • null is returned for the following situations:
    • the start_position is after the last character in the string
    • the start_position is negative
    • the length parameter is 0
  • If length is greater than the remaining length of the string only the remaining length of the string is returned.

Example

Function	Result
SUBSTRING('1234 Morrow Rd.', 1, 3)	123
SUBSTRING('Red Yellow Blue Red Green', 10)	w Blue Red Green
SUBSTRING('rwero234890dfsw' FROM 7)	34890dfsw
SUBSTRING('Q02CY2005' FROM 2 FOR 2)	02

---

Return to Built in Function details