REGEXP_INSTR
Searches a string for a regular expression pattern and returns an integer indicating the beginning position or ending position of the matched substring.
REGEXP_INSTR
supports the extended and advanced regular expression formats. You can set the global variable regexp_format
to specify which format you want to use.
Syntax
REGEXP_INSTR ( source_string, pattern [, position [, occurrence [, option [, parameters ] ] ] ] )
Arguments
source_string
A string expression, such as a column name, to be searched.
pattern
A string literal representing a regular expression.
position
Optional if occurrence
,option
and parameters
are not specified.
A positive integer indicating the position within source_string
to begin searching. The position is based on the number of characters, not bytes, so that multibyte characters are counted as single characters. The default is 1
. If position
is less than 1
, the search begins at the first character of source_string
. If position
is greater than the number of characters in source_string
, the result is 0
.
occurrence
Optional if option
and parameters
are not specified.
A positive integer indicating which occurrence of pattern
in source_string
to return. The search for the occurrence begins at position
.
If occurrence
is not present, defaults to 1
.
If occurrence
is less than 1
or greater than the number of characters in source_string
, the search is ignored and REGEXP_INSTR
returns 0
.
option
Optional if parameters
are not specified. If not present, defaults to 0
. If present, has one of the following values.
0
: Returns the position of the first character of the match.
1
: Returns the position of the first character following the end of the match.
Any other value: Has the same behavior when the value is 1
.
parameters
Optional. If present, has one of the following values.
c
: Perform case-sensitive matching.
i
: Perform case-insensitive matching.
If c
or i
is not specified, performs case-sensitive matching.
Remarks
If no match is found, then REGEXP_INSTR
returns 0
. If the source_string
is NULL
or the pattern
is NULL
, then REGEXP_INSTR
returns NULL
.
Examples
The following examples demonstrate how REGEXP_INSTR
works when the global variable regexp_format
is set to 'extended'
.
Call to | Result | Comments |
---|---|---|
|
| Returns the first position of |
|
| Returns the starting position of the first occurrence of a character followed by |
|
| Returns the position of the first digit. |
|
| Returns the position of the first alpha character. |
|
| Looks for |
|
| Returns the starting position of |
|
| Looks for |
|
| Returns the starting position of the first occurrence of |
|
| Returns the starting position of the second occurrence of |
|
| Returns the starting position of the second occurrence of |
|
| Returns the ending position + |
|
| Returns the starting position of the first occurrence of |
|
| Returns the starting position of the first occurrence of |
The following examples demonstrate how REGEXP_INSTR
works when the global variable regexp_format
is set to 'advanced'
.
Call to | Result | Comments |
---|---|---|
|
| Returns the position of the first digit. |
|
| Returns the position of the first non-digit. |
|
| Returns the position of the first alpha-numeric character. |
|
| Returns the position of the first non-alpha-numeric character. |
|
| Returns the starting position of the first occurrence of the word |
Using REGEXP_INSTR
With a Table
The following example shows how to call REGEXP_INSTR
once per table row. This example uses extended regular expression matching. Advanced regular expression matching could be used as well.
SET GLOBAL regexp_format = 'extended'; DROP DATABASE IF EXISTS singlestore_docs_example; CREATE DATABASE singlestore_docs_example; use singlestore_docs_example; CREATE TABLE example_table(id INT, text_field TEXT); INSERT INTO example_table VALUES (1, 'the'), (2, 'athe'), (3, 'abthe'), (4, 'abcthe'), (5, 'thethe');
SELECT id, text_field, REGEXP_INSTR(text_field,'..the') FROM regexample_table ORDER BY id; **** +------+------------+----------------------------------+ | id | text_field | REGEXP_INSTR(text_field,'..the') | +------+------------+----------------------------------+ | 1 | the | 0 | | 2 | athe | 0 | | 3 | abthe | 1 | | 4 | abcthe | 2 | | 5 | thethe | 2 | +------+------------+----------------------------------+