Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
REGEXP_ INSTR
On this page
Searches a string for a regular expression pattern and returns an integer indicating the beginning position or ending position of the matched substring.
REGEXP_
supports the extended and advanced regular expression formats.regexp_
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
andparameters
are not specified. -
A positive integer indicating the position within
source_
to begin searching.string 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 than1
, the search begins at the first character ofsource_
.string If position
is greater than the number of characters insource_
, the result isstring 0
.
-
occurrence: Optional if
option
andparameters
are not specified.This is how to specify the "nth occurrence" of a string. -
A positive integer indicating which occurrence of
pattern
insource_
to return.string The search for the occurrence begins at position
. -
If
occurrence
is not present, defaults to1
. -
If
occurrence
is less than1
or greater than the number of characters insource_
, the search is ignored andstring REGEXP_
returnsINSTR 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
ori
is not specified, performs case-sensitive matching.
-
Remarks
-
If no match is found, then
REGEXP_
returnsINSTR 0
.If the source_
isstring NULL
or thepattern
isNULL
, thenREGEXP_
returnsINSTR NULL
.
Examples
The following examples demonstrate how REGEXP_
works when the global variable regexp_
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_
works when the global variable regexp_
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_
once per table row.
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 example_tableORDER 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 |
+------+------------+----------------------------------+
And this example shows how to find the beginning of the 2nd occurrence of "the" in a row in the same table:
SELECT id, text_field, REGEXP_INSTR(text_field,'the',1,2) FROM example_tableORDER BY id;
+------+------------+------------------------------------+
| id | text_field | REGEXP_INSTR(text_field,'the',1,2) |
+------+------------+------------------------------------+
| 1 | the | 0 |
| 2 | athe | 0 |
| 3 | abthe | 0 |
| 4 | abcthe | 0 |
| 5 | thethe | 4 |
+------+------------+------------------------------------+
Last modified: December 6, 2023