Extracts substrings from strings using regular expressions.
REGEXP_SUBSTR( expression, regular-expression [, start-offset [ , occurrence-number [, escape-expression ] ] ] )
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 For 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 (\).
LONG VARCHAR
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 about 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 information about sub-character class matching, see Regular expressions: Special sub-character classes.
If start-offset is specified, that offset specifies the start of the expression to be matched. In particular, ^ matches the beginning of the expression starting at start-offset.
SQL/2008 Vendor extension. The corresponding function in the SQL/2008 standard is the SUBSTRING_REGEX function, which has similar parameters. SUBSTRING_REGEX is part of optional SQL/2008 language feature F844.
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; |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |