MariaDB – Regular Expression

MariaDB is also a relational database language that is similar to SQL. However, the introduction of MariaDB took place as it is an extension to SQL and contains some more advanced operators rather than SQL. MariaDB contains operators similar to SQL like CRUD operations and between operators and other operators like Limit and in such a way it also provides support to extract data from the database with the help of regular expressions. MariaDB is fast, scalable, and robust also it can be part of the database infrastructure for Big Data.

In this article, we will learn about the REGEX in MariaDB in detail along with its syntax, importance, practical implementations of examples, and so on.

Regular Expressions in MariaDB

Regular expressions are used to match the pattern given in a particular string. The regular expressions are mainly used to make the data extraction easier based on some conditions. In SQL language, we are aware of the LIKE operator which is used along with the % and _ characters to match the pattern after the specified condition.

In such a way we are aware of some of the regular expressions used in the programming languages such as Python and Java.

• In Python to work with regular expressions we will import the re-module to work with the regular expression.
• In Java, we import the java.util.regex package to work with the regular expressions.
• In the case of MariaDB, we use two clauses to match the regular expressions such as the similarLIKE clause and the REGEXP clause in MariaDB similar to SQL.

We use the regular expressions in the databases to extract the table records more easily. Instead of writing multiple conditions in the databases, we use regular expressions to extract the queries more easily and satisfy the conditions too.

Syntax for using REGEXP in MariaDB:

SELECT column_name FROM table_name WHERE column_name REGEXP 'pattern' ;

SELECT * FROM table_name WHERE column_name REGEXP 'pattern' ;

Generally, what are the patterns that are used? A pattern that is used in any language can be the combination of the below values.

Some Regular Expressions

Symbol	Description
*	The * character matches Zero or more occurrences of the specified alphabet or digit before *.
+	The + character matches One or more occurrences of the specified digit or alphabet before +.
?	The ? character matches one or more occurrences of the specified pattern that was mentioned before.
|	Just like the or symbol operator in programming languages. It matches any of the expression either before or after the | symbol,
[ ]	The symbol matches the characters that are specified only within the two square brackets.
[ ^ ]	The symbol matches the characters except the characters that are specified after the ^ symbol in the square brackets.
.	It Matches any character except null.
{ m }	It Matches the characters m times. Suppose if the m value was 2. It only matches the pattern two times even if it is repeated more than 2.
$	Generally it is used to match a specific pattern at the end of the string .
^	The ^ character is used to match the given pattern at the beginning of the string.
{m, }	The mentioned pattern matches characters in the string atleast m times .
{m,n}	The mentioned value matches atleast m times and atmost n times. It means it matches upto 8 characters in the string and greater than that .
\d	It only matches the numeric characters.
( )	It mainly matches the sub expression in the regular expression.
\w	It matches only alphabetic characters in the given string.
\S	It Matches white space characters in the pattern.

Example of Regular Expressions in MariaDB

To understand the Regex in the MariaDB we need a table on which we will perform various operations. So in this article we have a table called students which consist of id, name, subject, and GMAIL as Columns. After Inserting some data into the students table the table looks:

Output

Explanation: In the above image we have retrieved the data from the students.

Example 1: Extracting Records from the Students Table Whose Name Starts with Only Vowels

In this query we are going to extract the records from the students table whose name starts with only vowels.

Query:

SELECT * FROM students WHERE name REGEXP  '^[AEIOUaeiou][a-zA-Z]+';

Output:

Names start with vowels

Explanation:

• In the above image we have extracted name from the students table whose names starts with the vowels in the table.
• In the pattern we have first specified ^ it matches the beginning only the letters that starts with vowels.
• Next we have used [aeiouAEIOU] so that it matches the first letter vowel which may be either uppercase or lowercase.
• In the next we have specified [a-zA-Z]+ as we already know the + symbol matches one or more occurrences in the given
  string. It matches the string which contains only alphabets. Because name of any person contains only alphabets.

Example 2: Extracting Records from students Table Whose Name Starts With Consonant and Ends With the Vowels

In this query we are going to extract the records from the relation name students whose name starts with consonant and ends with vowel.

Query:

SELECT * FROM students WHERE name REGEXP '^[^AEIOUaeiou][a-zA-Z]+ ?[a-zA-Z]+[aeiouAEIOU]$' ;

Output:

names starts with vowels and consonants

Explanation:

• In the above image we have extracted all the records whose name starts with consonants and ends with the vowel.
• The ^ character is used to match the consonants at the starting of the string.
• The [^aeiouAEIOU] pattern matches all the characters except that were mentioned in the square brackets.
• Then after we have used [a-zA-Z]+ which matches all the words from one or more occurences.
• Then we have used ” “? which matches either zero or more occurences.It is because if the name contains firstname and lastname there will be a space between them.To match the whole name we have used that pattern.
• The [aeiouAEIOU]$ matches the vowels at the end of the string.

Example 3: Extracting Records from Student Table Whose Name has Length More than or Equal to 8.

In this query we are going to extract the records from the students table whose name is of length greater than or equal to 8.

Query:

SELECT * FROM students WHERE name REGEXP '[a-zA-Z]{8, }';

Output:

Names >=8

Explanation:

• In the above image we are extracting the records whose names length are greater than 8.
• In the above image we have used [a-A-Z ” “] to match all the alphabets that are present in the column name.We have also included the white space along with the alphabets to even match the space in the name.
• After that we have used { 8, } which matches atleast 8 alphabets present in the name column.
• It matches either 8 or more alphabets in the name.

Note: In the above pattern we have [a-zA-Z ” “] there was a white space to match the words that have space between them.

Example 4: Extracting Records from Student Table in which Email Starts with Alphabets and Contains Only Alphabets and Numbers Followed by Domain @gmail.com

In this we are going to extract the records from the students table where the email consists of only alphabets and numbers which ends with the domain name @gmail.com.

Query:

SELECT * FROM students WHERE GMAIL REGEXP '^[A-Za-z][a-zA-Z]+[0-9]+@gmail.com' ;

Output:

names with @gmail.com along with alphabets and numbers.

Explanation:

• In the above image we are extracting the email that consists of only starting with the alphabets followed by digits.
• The user name is to be followed by the domain name @gmail.com

Example 5: Extracting Records from Student Table Whose Name is Exactly of Length of Characters 5

In the query we are going to extract the records from the student table whose name is exactly of length 5.

Query:

SELECT * FROM students WHERE name REGEXP "^[A-Za-z]{5}$" ;

Output:

NAMES WITH LENGTH 5

Explnation:

• In the above image we have used the regular expressions to extract the name which is of exactly length 5 .
• The ^ character is used to match the pattern at the beginning of the string.
  
• The [a-zA-Z] is used to match only the alphabets in the string.
• The ^ and $ are used to match the alphabets at the starting and ending of the string.

Explanation 6: Extracting Records From the Table Where Name Ends With “a” or “A”

In this query we are going to extract the records from the students whose name ends with the a or A.

Query:

SELECT * FROM students WHERE name REGEXP "^[A-Za-z][a-zA-Z ]+[Aa]$";

Output:

OUTPUT NAMES END WITH A or a

Explanation:

• In the above image first the name should be started with the alphabet so we have used the pattern ^[a-A-Z].
• In the above image after a alphabet is matched then the words are matched along with ” “ because we have mentioned the white space in [a-zA-Z ” “]+ or \S in words.
• After that [aA]$ which matches either a or A at the end of the string.
• In this way we have matched the string that ends with the a or A.

Conclusion

The regular expressions shortly known as REGEX, they are too powerful which can easily perform the data pre-processing tasks in a easier manner. They are used to identify and parsing the data present in the relations [tables]. They are used to replace the data and also helpful in managing and manipulating the data in the database. The regular expressions are also used in many database related languages such as SQL , MySQL and even in non relational database languages such as MongoDB [ $regex operator ]. Regex queries can be optimized for performance even with large datasets.