Basic syntax

The basic syntax for a regular expression is as follows HERE –

“SELECT statements…” is the standard SELECT statement

“WHERE fieldname” is the name of the column on which the regular expression is to be performed on.

“REGEXP ‘pattern’” REGEXP is the regular expression operator and ‘pattern’ represents the pattern to be matched by REGEXP. RLIKE is the synonym for REGEXP and achieves the same results as REGEXP. To avoid confusing it with the LIKE operator, it better to use REGEXP instead.

Let’s now look at a practical example- The above query searches for all the movie titles that have the word code in them. It does not matter whether the “code” is at the beginning, middle or end of the title. As long as it is contained in the title then it will be considered. Let’s suppose that we want to search for movies that start with a, b, c or d , followed by any number of other characters, how would we go about to achieve that. We can use a regular expression together with the metacharacters to achieve our desired results. Executing the above script in MySQL workbench against the myflixdb gives us the following results. Let’s now take a close look at our regular expression responsible for the above result. ‘^[abcd]’ the caret (^) means that the pattern match should be applied at the beginning and the charlist [abcd] means that only movie titles that start with a, b, c or d are returned in our result set. Let’s modify our above script and use the NOT charlist and see what results we will get after executing our query. Executing the above script in MySQL workbench against the myflixdb gives us the following results. Let’s now take a close look at our regular expression responsible for the above results. ‘^[^abcd]’ the caret (^) means that the pattern match should be applied at the beginning and the charlist [^abcd] means that the movie titles starting with any of the enclosed characters is excluded from the result set.

Regular expression metacharacters

What we looked at in the above example is the simplest form of a regular expression. Let’s now look at more advanced regular expression pattern matches. Suppose we want to search for movie titles that start with the pattern “code” only using a regular expression, how would we go about it? The answer is metacharacters. They allow us to fine tune our pattern search results using regular expressions. The backslash () is used to as an escape character. If we want to use it as part of the pattern in a regular expression, we should use double backslashes (\) gives all the movies with titles starting with the characters. For Example: Forgetting Sarah Marshal. gives all the movies with titles ending with the characters “ack” .For Example, Code Name Black. [:alpha:] to match letters, [:space:] to match white space, [:punct:] is match punctuations and [:upper:] for upper class letters. gives all the movies with titles contain letters only .For Example, Forgetting Sarah Marshal, X-Men etc. Movie like Pirates of the Caribbean 4 will be omitted by this query.

Summary

	Regular expressions provide a powerful and flexible pattern match that can help us implement power search utilities for our database systems.

	REGEXP is the operator used when performing regular expression pattern matches. RLIKE is the synonym

	Regular expressions support a number of metacharacters which allow for more flexibility and control when performing pattern matches.

	The backslash is used as an escape character in regular expressions. It’s only considered in the pattern match if double backslashes have used.

	Regular expressions are not case sensitive.