MariaDB Tutorial MariaDB Advanced MariaDB Database Account Management MariaDB References

MariaDB REGEXP_SUBSTR() Function



The MariaDB REGEXP_SUBSTR() function returns the substring of the string that matches the regular expression specified by the pattern. It returns NULL if there is no match. If the string or pattern is NULL, the return value will be NULL.

By default, REGEXP_SUBSTR() function performs a case-insensitive match, except when used with binary strings.

Syntax

REGEXP_SUBSTR(string, pattern)

Parameters

string Required. Specify the string to search.
pattern Required. Specify the regular expression matching information. See the table below for pattern syntax.

Regular Expression Patterns

SymbolDescription
^Matches the beginning of a string.
$Matches the end of a string.
*Matches zero or more occurrences.
+Matches one or more occurrences.
?Matches zero or one occurrence.
.Matches any character except NULL.
|Used like an "OR" to specify more than one alternative.
[ ]Matches any single character specified within [].
[^ ]Matches any single character that is not specified within [].
-Represents a range of characters.
( )Used to group expressions as a subexpression.
{m}Matches m times.
{m,}Matches at least m times.
{m,n}Matches at least m times, but no more than n times.
\nn is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
\dMatches a digit character.
\DMatches a nondigit character.
\wMatches a word character.
\WMatches a nonword character.
\sMatches a whitespace character.
\Smatches a non-whitespace character.
*?Matches the preceding pattern zero or more occurrences.
+?Matches the preceding pattern one or more occurrences.
??Matches the preceding pattern zero or one occurrence.
{n}?Matches the preceding pattern n times.
{n,}?Matches the preceding pattern at least n times.
{n,m}?Matches the preceding pattern at least n times, but not more than m times.
[..]Matches one collation element that can be more than one character.
[=character_class=]Represents an equivalence class. It matches all characters with the same collation value, including itself.
[:character_class:]Represents a character class that matches all characters belonging to that class.

List of standard class name

Character Class NameMeaning
alnumAlphanumeric characters
alphaAlphabetic characters
blankWhitespace characters
cntrlControl characters
digitDigit characters
graphGraphic characters
lowerLowercase alphabetic characters
printGraphic or space characters
punctPunctuation characters
spaceSpace, tab, newline, and carriage return
upperUppercase alphabetic characters
xdigitHexadecimal digit characters

Example: REGEXP_SUBSTR() examples

The example below shows the usage of REGEXP_SUBSTR() function.

SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
Result: 'abc'

REGEXP_SUBSTR('ab123cd','[0-9]+');
Result: '123'

Example: Case-sensitive search

There are many ways in MariaDB which can be used for case-sensitive search while using this function. See the example below:

SELECT REGEXP_SUBSTR('ABC','b');
Result: B

/* using COLLATE to convert string to a binary string */
SELECT REGEXP_SUBSTR('ABC' COLLATE utf8_bin,'b');
Result: NULL

/* using BINARY to convert string to a binary string */
SELECT REGEXP_SUBSTR(BINARY'ABC','b');
Result: NULL

/* forces case-sensitive using (?-i) pattern */
SELECT REGEXP_SUBSTR('ABC','(?-i)b');
Result: NULL

/* forces case-insensitive search */
SELECT REGEXP_SUBSTR('ABC' COLLATE utf8_bin,'(?i)b');
Result: B

/* forces case-insensitive search */
SELECT REGEXP_SUBSTR('ABC' COLLATE utf8_bin,'(?+i)b');
Result: B

Example: Match on more than one alternative

Consider a database table called Employee with the following records:

EmpIDNameCityAge
1John SmithLondon25
2Marry KnightNew York24
3Jo WilliamsParis27
4Smith FischerAmsterdam30
5Aman GuptaNew Delhi28


The below query is used to get the first vowel in the records of Name column. To specify more than one alternative in the pattern, | is used.

SELECT *, 
REGEXP_SUBSTR(Name, 'a|e|i|o|u') AS First_Vowel
FROM Employee

This will produce a result similar to:

EmpIDNameCityAgeFirst_Vowel
1John SmithLondon25o
2Marry KnightNew York24a
3Jo WilliamsParis27o
4Smith FischerAmsterdam30i
5Aman GuptaNew Delhi28A

Example: Matching on words

Consider the above discussed table. The below query is used to get the first word (first name) from the Name column. To specify a range of characters, [  ] is used

SELECT *, 
REGEXP_SUBSTR(Name, '[a-z]+') AS FirstName
FROM Employee

This will produce a result similar to:

EmpIDNameCityAgeFirstName
1John SmithLondon25John
2Marry KnightNew York24Marry
3Jo WilliamsParis27Jo
4Smith FischerAmsterdam30Smith
5Aman GuptaNew Delhi28Aman

❮ MariaDB - REGEXP