MySQL - Regular Expressions

Hello, aspiring database enthusiasts! Today, we're diving into the fascinating world of MySQL Regular Expressions. Don't worry if you've never coded before - we'll start from the basics and work our way up. By the end of this tutorial, you'll be wielding regular expressions like a pro!

What are Regular Expressions?

Before we jump into MySQL specifics, let's understand what regular expressions (regex) are. Imagine you're trying to find a specific pattern in a haystack of text. That's where regex comes in handy! It's like a supercharged search tool that can find complex patterns in strings.

MySQL Regular Expressions

In MySQL, we use the REGEXP operator to work with regular expressions. It's similar to the LIKE operator but much more powerful.

Basic Syntax

Here's the basic syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name REGEXP 'pattern';

Let's look at some examples:

SELECT * FROM employees WHERE last_name REGEXP 'son';

This query will find all employees whose last names contain 'son', like Johnson, Wilson, or Sonny.

Case Sensitivity

By default, MySQL regex is case-insensitive. If you want case-sensitive matching, use the BINARY keyword:

SELECT * FROM employees WHERE last_name REGEXP BINARY 'Smith';

This will match 'Smith' but not 'smith' or 'SMITH'.

Patterns used with REGEXP

Now, let's explore some common patterns used with REGEXP. I like to think of these as the "secret code" of regex - once you know them, you can find almost anything!

^ (Caret) - Start of String

The caret symbol matches patterns at the beginning of a string.

SELECT * FROM products WHERE product_name REGEXP '^Apple';

This finds all products whose names start with 'Apple', like 'Apple iPhone' or 'Apple MacBook'.

$ (Dollar) - End of String

The dollar sign matches patterns at the end of a string.

SELECT * FROM products WHERE product_name REGEXP 'Pro$';

This finds products ending with 'Pro', like 'MacBook Pro' or 'iPad Pro'.

. (Dot) - Any Single Character

The dot matches any single character except newline.

SELECT * FROM words WHERE word REGEXP 'c.t';

This matches words like 'cat', 'cut', or even 'c@t'!

* (Asterisk) - Zero or More Occurrences

The asterisk matches zero or more occurrences of the previous character.

SELECT * FROM emails WHERE email REGEXP 'info.*@example.com';

This matches emails like '[email protected]', '[email protected]', or even '[email protected]'.

+ (Plus) - One or More Occurrences

Similar to *, but requires at least one occurrence.

SELECT * FROM products WHERE product_name REGEXP 'iPad ?Pro+';

This matches 'iPad Pro', 'iPad Pro', 'iPad Pro', etc.

? (Question Mark) - Zero or One Occurrence

The question mark makes the previous character optional.

SELECT * FROM words WHERE word REGEXP 'colou?r';

This matches both 'color' and 'colour'.

[] (Square Brackets) - Character Set

Square brackets define a set of characters to match.

SELECT * FROM employees WHERE last_name REGEXP '[AEIOU]';

This finds last names containing any vowel.

[^] (Negated Character Set)

Adding a caret inside square brackets negates the set.

SELECT * FROM products WHERE product_name REGEXP '[^0-9]';

This finds product names that don't contain any digits.

| (Pipe) - Alternation

The pipe symbol acts like an OR operator.

SELECT * FROM animals WHERE species REGEXP 'cat|dog';

This finds all cats and dogs in our animal table.

Regular Expression Functions and Operators

MySQL provides several functions and operators for working with regular expressions. Let's explore them:

Function/Operator	Description
REGEXP	Matches a string against a regular expression
REGEXP_LIKE()	Returns 1 if the string matches the regex pattern, 0 otherwise
REGEXP_INSTR()	Returns the starting index of the first regex match
REGEXP_REPLACE()	Replaces substrings that match a regex pattern
REGEXP_SUBSTR()	Returns the substring that matches a regex pattern

Let's see some examples:

REGEXP_LIKE()

SELECT * FROM products WHERE REGEXP_LIKE(product_name, '^iPhone');

This finds all products whose names start with 'iPhone'.

REGEXP_INSTR()

SELECT email, REGEXP_INSTR(email, '@') AS at_position
FROM users;

This returns the position of the '@' symbol in each email address.

REGEXP_REPLACE()

SELECT REGEXP_REPLACE('Hello, World!', '[aeiou]', '*') AS vowels_replaced;

This replaces all vowels with asterisks, returning 'Hll, W*rld!'.

REGEXP_SUBSTR()

SELECT REGEXP_SUBSTR('[email protected]', '[^@]+') AS username;

This extracts the username part of an email address.

Conclusion

Congratulations! You've just taken your first steps into the powerful world of MySQL regular expressions. Remember, practice makes perfect. Try creating your own regex patterns and experiment with different combinations.

As you continue your journey, you'll find that regex is like a Swiss Army knife in your database toolkit. It might seem a bit tricky at first, but once you get the hang of it, you'll wonder how you ever managed without it!

Happy coding, and may your queries always return the results you're looking for!

Credits: Image by storyset