REGEXP_SUBSTR function [String]

Extracts substrings from strings using regular expressions.

Syntax 
REGEXP_SUBSTR( expression, 
regular-expression 
[, start-offset [ , occurrence-number [, escape-expression ] ] ] )
Parameters

  • expression   The string to be searched.

  • regular-expression   The pattern you are trying to match. For more information about regular expression syntax, see Regular expressions overview.

  • start-offset   The offset into expression at which to start searching. start-offset is expressed as a positive integer, and reflects the number of characters to count when starting from the left side of the string. The default is 1 (the start of the string).

  • occurrence-number   In the case of multiple matches within expression, specify an integer indicating the occurrence to locate. For example, 3 finds the third occurrence. The default is 1.

  • escape-expression   The escape character to use for regular-expression. The default is the backslash character (\).

Returns

LONG VARCHAR

Remarks

REGEXP_SUBSTR returns NULL if regular-expression is not found.

Similar to the REGEXP search condition, the REGEXP_SUBSTR function uses code points for matching and range evaluation. This means that database case sensitivity does not impact results. For more information on how REGEXP_SUBSTR performs matching and set evaluation, see LIKE, REGEXP, and SIMILAR TO: differences in character comparisons.

When matching against a character class that contains only a sub-character class, include the outer square brackets and the square brackets for the sub-character class (for example, REGEXP_SUBSTR (expression, '[[:digit:]]')). For more on sub-character class matching, see Regular expressions: Special sub-character classes.

See also
Standards and compatibility

  • SQL/2003   Vendor extension.

Example

The following example breaks values in the Employees.Street column into street number and street name:

SELECT REGEXP_SUBSTR( Street, '^\S+' ) as street_num, 
 REGEXP_SUBSTR( Street, '(?<=^\S+\s+).*$' ) AS street_name 
    FROM Employees;
street_num street_name
9 East Washington Street
7 Pleasant Street
539 Pond Street
1244 Great Plain Avenue
... ...

To determine whether the IP address of the current connection is in a range of IP addresses (in this case, 10.25.101.xxx or 10.25.102.xxx), you can execute the following statement: 

IF REGEXP_SUBSTR( CONNECTION_PROPERTY( 'NodeAddress' ), '\\d+\\.\\d+\\.\\d+' )
   IN ( '10.25.101' , '10.25.102' ) THEN 
      MESSAGE 'In range' TO CLIENT;
ELSE 
      MESSAGE 'Out of range' TO CLIENT;
END IF;
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1