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

Regular Expression Patterns

List of standard class name

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:

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:

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: