MariaDB Regexp + Examples

In this MariaDB tutorial, we are going to learn about the “MariaDB regexp” using various examples in MariaDB. Additionally, we will cover the following topics.

• MariaDB regexp_replace
• MariaDB regexp
• MariaDB regexp_substr
• MariaDB regexp_like
• MariaDB regexp_instr
• MariaDB regexp_replace does not exist
• MariaDB regexp_substr multiple
• MariaDB regexp_replace multiline
• MariaDB regexp in where clause
• MariaDB regexp_replace space

MariaDB regexp

In MariaDB, the attribute REGEXP returns the boolean result is True (1) or False (0) based on the matching condition. If the pattern matches with the expression then the result is True or one, otherwise if not matches then False or zero.

The syntax of the REGEXP is given below.

expression REGEXP pattern

Where,

• expression: It is a string or expression.
• REGEXP: It is the attribute that performs the pattern matching against the expression.
• pattern: It is the regular expression that is an actual pattern.

Let’s check an example with the table. Here we will use the Person table that contains the information about person name,birth_date, and birth_place. The description of the table is given below.

Check the person whose birthplace is the United State using the attribute REGEXP using the below code.

SELECT person_name, birth_place REGEXP 'United State' as person_birthplace FROM Person;

In the above code, we are selecting the columns person_name whose birthplace is the United State from a table Person.

Here in the code, expression is the column birthplace and pattern is the word ‘United State’.

Look in the output, the column’s birthplace contains the value zero for all persons except the ‘Kenneth S. Covington’. ‘Kenneth S. Covington’ has value one the column birthplace because he was born in the United State.

Read: Replace Function in MariaDB

MariaDB regexp_replace

In MariaDB, the function REGEXP_REPLACE replaces the specific string based on pattern with a new string of a string.

The syntax of the REGEXP_REPLACE is given below.

REGEXP_REPLACE(subject, pattern, replace)

Where,

• REGEXP_REPLACE: It is the function that replaces all occurrences of a specific string with a string based on the pattern specified.
• subject: It is the main string or the string whose some of the value is replaced.
• pattern: It is the regular expression that acts as a pattern for the subject.
• replace: It is the string that is placed wherever the specified pattern matches with the subject.

Let’s take an example with a table Person that we have used in the above sub-section.

SELECT REGEXP_REPLACE(person_name,'e','i') FROM person;

In the above code, the subject is the column person_name, the pattern is the character ‘e‘ and the pattern is the character ‘i‘.

From the output, we can see that the character ‘e‘ is replaced by the character ‘i’.

Let’s take another example using the simple word in the SELECT statement only.

SELECT REGEXP_REPLACE('United Kindoms','s','');

In the above code, the subject is the string 'United Kindoms', the pattern is the character 's' and the replace the string is the (double single quote that represents nothing) ''.

The code is replacing the character ‘s’ with nothing which is represented by the double single quote(”’).

From the output, it is shown that the character ‘s‘ is replaced with nothing of string ‘United Kindoms‘ as a result of United Kindom.

Also, check: MariaDB AUTO_INCREMENT

MariaDB regexp_substr

In MariaDB, the REGEXP_SUBSTR is a function that returns the subpart of the string based on the specified regular expression.

The syntax of the REGEXP_SUBSTR is given below.

REGEXP_SUBSTR(subject,pattern);

Where,

• REGEXP_SUBSTR: It is the function that accepts the subject (string) and pattern (regular expression) return the subpart of the subject.
• subject: It is the string whose subparts are returned based on the specified pattern.
• pattern: It is the regular expression that act as pattern and matches with subject.

Let’s check with an example using the table.

SELECT birth_place,REGEXP_SUBSTR(birth_place,'United') FROM person;

Here in the code, the subject is the column birth_place of the table Person and the pattern is the word ‘United‘.

As we can see in the above output, The REGEXP_SUBSTR returns the word ‘United’ as specified in the pattern from each birthplace that starts with the word ‘United’.

Let’s check with another example using the simple word in the SELECT statement.

SELECT REGEXP_SUBSTR('United Kindoms','United');

Where the word 'United Kindoms' is a subject and 'United'is a pattern. The above code will return the subpart of the subject which is the word United.

From the above output, the REGEXP_SUBSTR function returns the ‘United’ as the subpart of the string ‘United Kindoms‘.

Read: MariaDB Primary Key

MariaDB regexp_like

In MariaDB, there is no function like REGEXP_LIKE but it has the attribute like RLIKE that returns the one if the pattern matches with the string otherwise returns zero.

• The RLIKE is like the attribute REGEXP that we have used above.

The syntax of the RLIKE is given below.

expression RLIKE pattern

Where,

• expression: It is a string or expression.
• RLIKE: It is the attribute that performs the pattern matching against the expression.
• pattern: It is the regular expression that is an actual pattern.

Let’s look at the same example that we have used in the sub-section “MariaDB regexp”.

SELECT person_name, birth_place RLIKE 'United State' as person_birthplace FROM Person;

In the above code, we are selecting the column person_name whose birthplace is the United State from a table Person.

Here in the code, expression is the column birthplace and pattern is the word ‘United State’.

From the output, we can see that only ‘Kenneth S. Covington’ has a birthplace value of 1. which means this person was born in a country like ‘United State’.

Read: MariaDB Full Outer Join

MariaDB regexp_substr multiple

In MariaDB, the REGEXP_SUBSTR function returns the first match of the pattern with the string. Suppose if we match ‘a‘ with a string containing a character 'a' at nine places then, this function will return only the first match by default.

From the above sub-section, we already know about the use of REGEXP_SUBSTR, let’s check with an example.

SELECT REGEXP_SUBSTR('United State','t');

Where,

• REGEXP_SUBSTR: It is the function that accepts the subject (string) and pattern (regular expression) return the subpart of the subject.
• subject: It is the string whose subparts are returned based on the specified pattern. Here subject is the string United State.
• pattern: It is the regular expression that acts as a pattern and matches with the subject. Here pattern is the character t.

From the output, we can see that the string “United State” contains the multiple characters of 't' or pattern matches with the string multiple times but returns only the first match.

Read: MariaDB Substring

MariaDB regexp_replace multiline

In MariaDB, the function REGEXP_REPLACE can replace the multiline text with other symbols or characters.

Let’s understand with an example by creating the new table as Quotes, this table will contain the famous quotes in multiline.

CREATE TABLE `Quotes` (
  `entry` text NOT NULL
);

• CREATE TABLE: It is the command to create a new table, here we are creating the table as Quotes.
• entry: It is the name of the column that will contain the text related to Quotes, this column is also defined as the NOT NULL.

Insert the following record as text in a table using the below code.

INSERT INTO `Quotes` (`entry`) VALUES ('The purpose of our lives is to be happy\r\n
Life is what happens when you’re busy making other plans\r\n
Get busy living or get busy dying\r\n
Never let the fear of striking out keep you from playing the game\r\n
Money and success don’t change people; they merely amplify what is already there');

The records contain five different quotes. As we can see at the end of each quote \n is used, this \n represents the new line.

Now view the table using the below code.

SELECT * FROM Quotes;

From the output, we can see that each quote is in a separate line. So here we want to replace all the quotes between the 1 and 9th quotes.

To replace all the quotes separated by a line except the 1 and 9th, use the below code.

SELECT REGEXP_REPLACE(entry, '(?s)Life is what happens when you’re busy making other plans.*
Never let the fear of striking out keep you from playing the game\r\n', '') FROM Quotes;

After running the above code, we can in the below output that it replaced all the quotes in separate lines except first and last.

Read: MariaDB LIMIT + Examples

MariaDB regexp_instr

In MariaDB, the REGEXP_INSTR returns the position of the string or character based on the specified pattern. It returns the first occurrences of the matching pattern against the string.

The syntax of the REGEXP_INSTR is given below.

REGEXP_INSTR(subject, pattern);

Where,

• REGEXP_INSTR: It is the function that returns the position of the matched pattern against the subject (string).
• subject: It is the string.
• pattern: It is the regular expression that is matched with the subject.

Let’s take an example using the table Person that we have used in the above first sub-section.

SELECT person_name, REGEXP_INSTR(person_name,'e') FROM Person;

In the output, we can see that the REGEXP_INSTR returns the position of the character ‘e’ in the column person_name.

The demonstration of the REGEXP_INSTR using the simple word in the SELECT statements given below.

SELECT REGEXP_INSTR('United States','t');

Here in the code, subject is the word 'United States' and the pattern is the character 't'.

As the output shows that the position of the character ‘t‘ in the word 'United States' is 4. But we can see there is more character as ‘t ‘ in 'United States'. So here the function REGEXP_INSTR returns the position of the first occurrences of the matching pattern against the string.

Read: MariaDB index with Examples

MariaDB regexp_replace does not exist

In MariaDB, this kind of error arises for the old version of the MariaDB server. if you are using the MariaDB 5.5 version then this kind of error may arise. To resolve this kind of error upgrade the MariaDB server version from 5.5 to the latest version like MariaDB 10.0.

The official like is here Upgrading from MariaDB 5.5 to MariaDB 10.0.

MariaDB regexp in where clause

In MariaDB, the REGEXP attribute matches the expression against the pattern.

The syntax of the REGEXP is given below.

expression REGEXP pattern

Where,

• expression: It is the string expression.
• pattern: It is the extended regular expression that matches with expression.

Here we will use the employee whose description or information is given below.

Let’s check with an example using the below code.

SELECT first_name,city FROM employee
WHERE city REGEXP 'New';

In the above code, we are selecting the employee who belongs to a city that starts with the word ‘New‘. Here the city is an expression and the word ‘New‘ is a pattern. Here we have used the REGEXP within the WHERE clause.

As from the output, Lind and David are from the city of New York.

Read: MariaDB LIKE Operator

MariaDB regexp_replace space

In MariaDB, the space can be replaced with any characters using the function REGEXP_REPLACE. As we already know about the REGEXP_REPLACE function that is described in the above function.

Let’s directly jump to an example. Here we will use the simple word to remove space from that word.

SELECT REGEXP_REPLACE('United States',' ','');

Here in the above code, subject is the word 'United States', pattern is a space that is represented using the double single quote with space within the double single quote. And the replace is nothing that is represented using the double single quote only.

The output shows that the no space between the word United States as we provided to the REGEXP_REPLACE function.

Now take another example with a table Person.

SELECT * FROM Person;

In the column, person_name has first and last names separated with space. Use the below code to replace the space with nothing.

SELECT person_name, REGEXP_REPLACE(person_name,' ','') FROM Person;

Look at the output in both columns, the first column person_name is an original column containing the name with space. The second is a column that contains the name without space after applying the REGEXP_REPLACE function on the first column.

Also, take a look at some more MariaDB tutorials.

• How to import CSV files in MariaDB
• MariaDB Timestamp + Examples
• MariaDB Join with Examples
• MariaDB Select Statement
• How to Create Trigger in MariaDB
• How to Create Function in MariaDB
• MariaDB Select Into + Examples

So, in this tutorial, we have learned about the “MariaDB regexp_replace” and covered the following topics.

• MariaDB regexp_replace
• MariaDB regexp
• MariaDB regexp_substr
• MariaDB regexp_like
• MariaDB regexp_instr
• MariaDB regexp_replace does not exist
• MariaDB regexp_substr multiple
• MariaDB regexp_replace multiline
• MariaDB regexp in where clause
• MariaDB regexp_replace space