Action disabled: source

Development: Omnidex SQL

SQL Functions: SUBSTRING

Description

The SUBSTRING function returns a new character string that is a portion of another character expression. The new string begins at the specified starting point in the original expression and extends the number of characters specified in the length parameter. If the length parameter is omitted, the new string extends to the end of the original string.

This function returns a STRING datatype.

Syntax

SUBSTRING( character_expression FROM numeric_expression [ FOR numeric_expression ] )
character_expression

Required. Any expression that returns a character-class datatype to be used as the substring target. Examples of expressions are columns, concatenations of columns, literal strings and functions that return a character datatype.

numeric_expression

Any expression that returns a numeric_class datatype to be used as either the offset or the length.

The FROM numeric_expression indicates the number of characters from the start of the character_expression to start the extraction of the partial string.

String M y s t r i n g
Position 1 2 3 4 5 6 7 8 9

If the start_no was 4 in the string above, then 's' would be the first character of the returned substring.

Example

Example 1: Substring of column

> select name, 
>>       substring(phone from 2 for 3) area_code, 
>>       substring(phone from 7 for 8) phone_number 
>>  from individuals;

NAME                                                ARE  PHONE_NU
--------------------------------------------------  ---  --------
MS. MARY LEWIS                                      203  318-1659
MRS. ALICE R TOLLIVER
MR. SIDNEY K TOLLIVER
MRS. JENNIFER J HOPKINS                             860  998-2013
MR. CHARLIE HOPKINS
MS. KIMBERLY BLUE
KATHLEEN TUCKER

Example 2: Complex expression

The following example removes the salutation from the name.

<code>
> select        trim(
>>                 substring(NAME
>>                   from position('.' in NAME) + 1
>>                   for $column_length(NAME) - position('.' in NAME))) nme
>>   from        INDIVIDUALS;

NME
--------------------------------------------------
MARY LEWIS
ALICE R TOLLIVER
SIDNEY K TOLLIVER
JENNIFER J HOPKINS
CHARLIE HOPKINS
KIMBERLY BLUE
KATHLEEN TUCKER

Additional Resources

See also:

 
Back to top
dev/sql/functions/substring.txt ยท Last modified: 2016/06/28 22:38 (external edit)