Regular Expression Functions
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.
On this page
Using Regular Expression Commands
Suppose you want to find the starting position of a string within another string.dog
within The dog chased the cat
, you would run the query SELECT INSTR('The dog chased the cat','dog');
.5
, indicating the position where it found dog
.
But INSTR does not allow search strings that contain placeholders.?
symbol.
Search String |
Placeholder |
---|---|
|
Any string |
|
Any three letter string |
|
The string |
Regular expressions enable you to define search strings with placeholders.The .
matches many target strings.The dog chased the frog
and The rat chased the mouse
.
Patterns contain literals and metacharacters.
The pattern The .
contains the literals The
,chased
,the
,mouse
and frog
..
,{3,5}
and |
.
Regular Expression Functions
SingleStore has five built-in functions that allow you to work with regular expressions.
-
REGEXP
: Searches a string for a regular expression pattern and returns1
if the string is found,0
otherwise. -
RLIKE
: Has the same functionality asREGEXP
. -
REGEXP_
: Searches a string for a regular expression pattern and returns an integer indicating the beginning position or ending position of the matched substring.INSTR -
REGEXP_
: Searches a string for a regular expression pattern and returns the matching substring.SUBSTR -
REGEXP_
: Searches a string for the first occurrence of a regular expression pattern and replaces that occurrence with a replacement string.REPLACE Can also perform a search and replace of all occurrences. -
REGEXP_
: Returns a JSON array of matching substring(s) within the first match of a regular expression pattern to a string.MATCH It supports both extended and advanced regular expression formats. You can set the global variable REGEXP_
to specify which format you want to use.FORMAT
Regular Expression Format - Advanced Regular Expression (ARE)
SingleStore supports advanced regular expressions (ARE) as defined in PostgreSQL.
You can use the ARE format with the built-in SingleStore functions RLIKE
, REGEXP
,REGEXP_
,REGEXP_
, and REGEXP_
, which are described in the next section.
Set the global variable regexp_
to specify the regular expression format to be used by the built-in functions you call.
To use ARE globally, run:
SET GLOBAL regexp_format=advanced;
You should set regexp_
to 'advanced'
if you are developing new regular expression logic.
Advanced Regular Expression (ARE) Support
SingleStore supports ARE as defined in PostgreSQL.
Backreferences
A pattern containing a backreference matches the same string that it matched earlier in the string.\\n
, where n
is the number of the backreference.
For example, the pattern a(bc)d(ef)g\\1\\2
matches abcdefgbcef
.
Escapes
Escapes make it easier to write some types of patterns.
Metacharacter |
Meaning |
---|---|
|
Matches any digit. |
|
Matches a space. |
|
Matches any alpha-numeric character including the underscore. |
|
Matches any character except a digit. |
|
Matches any character except a space. |
|
Matches any non-alpha-numeric character. |
|
Matches the beginning of a word. |
|
Matches the end of a word. |
Note
In PostgreSQL, escapes in patterns begin with one \
character.
In SingleStore, escapes in patterns also begin with one \
character.\
literal in an SingleStore string needs to escaped with a leading \
.\
characters.SELECT REGEXP_
In this section
Last modified: July 29, 2024