Using Regular Expressions in SQL

When you need to match patterns in strings, regular expressions are often the most powerful tool available. They provide a flexible way to describe and identify character patterns within larger strings.

Fundamental Regex Patterns

Understanding these core building blocks is essential for writing effective patterns:

1. ^: Anchors the match to the beginning of a string or line. This ensures your pattern only matches when found at the start.
2. [a-z]: Defines a character class matching any single lowercase letter from a through z. The hyphen indicates a range.
3. [0-9]: Similar to the previous example, this matches any single digit from 0 to 9. You can combine multiple ranges as needed.
4. [a-zA-Z]: Matches both uppercase and lowercase letters. Character classes support multiple ranges concatenated within the same brackets, giving you fine-grained control over acceptable characters.
5. [^a-z]: The caret inside brackets negates the character class, matching anything NOT in the specified range. Note that the meaning of ^ changes dramatically depending on whether it appears inside or outside square brackets.
6. [a-z]*: The asterisk quantifier matches zero or more occurrences of the preceding character or group, allowing flexible matching of variable-length sequences.
7. [a-z]+: The plus sign requires at least one occurrence, matching one or more characters. This differs from the asterisk which permits zero matches.
8. .: The period wildcard matches any single character, making it useful when the specific character doesn't matter but something must be present.
9. \.: Escapes special characters like the period itself. Since periods have special meaning in regex (matching any character), you need backslash escaping to match a literal period. In many programming languages, you'll need double escaping—first for the string parser, then for the regex engine—resulting in \\..
10. $: Anchors the match to the end of a string, ensuring your pattern only matches when found at the conclusion.

Practical Application: Valid Email Validation

Consider a common database problem: validating email addresses against specific criteria. This demonstrates how regex patterns solve real-world data validation challenges.

Requirements breakdown:

• The local part (before @) must start with a letter: ^[a-zA-Z]
• Following characters can include letters, numbers, underscores, periods, and hyphens: [a-zA-Z0-9_.-]
• Must end with the specific domain: @leetcode\\.com$

SELECT user_id, name, mail
FROM Users
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*\\@leetcode\\.com$';

Pattern Matching in Medical Records

Another common scenario envolves searching through condition descriptions. In medical databases, you might need to find patients with specific diagnosis codes regardless of their position within the string.

Problem: Finding DIAB1-related conditions

The challenge here is that "DIAB1" can appear in different positions—either at the beginning of the string or following a space within a multi-word description. This requires a pattern that accounts for multiple valid locations.

SELECT *
FROM patients
WHERE conditions REGEXP '^DIAB1|\\sDIAB1';

The pipe symbol (|) acts as an OR operator, allowing the regex engine to try multiple pattern alternatives. Matching proceeds from left to right, stopping at the first successful match. The \\s metacharacter represents whitespace characters, including spaces, tabs, and newline characters.

Understanding Metacharacters: Word Boundaries and Whitespace

Two particularly useful metacharacters deserve special attention:

Word Boundary (\b)

• The \b assertion matches positions where a word character (alphanumeric) meets a non-word character (or the string boundaries)
• For instance, \bcode\b matches "access code" but not "encoded" because in the latter case, "code" isn't a complete word
• This proves invaluable when you need to match whole words rather than substrings

Whitespace Characters (\s)

• The \s metacharacter matches any whitespace character: spaces, tabs, form feeds, and line breaks
• Patterns like \s+ match one or more consecutive whitespace characters
• This proves essential when parsing semi-structured text where spacing might vary

Here's how the word boundary approach looks:

SELECT * FROM patients WHERE conditions REGEXP '\\bDIAB1'

Note that MySQL requires double backslashes because the string parser consumes one level of escaping before passing the pattern to the regex engine.

Alternative Approach: Using LIKE Operator

For simpler patterns, MySQL's LIKE operator offers an alternative to REGEXP, though with more limited capabilities:

Wildcard patterns:

• %: Matches zero or more characters in any position. 'start%' finds strings beginning with "start", '%end' finds strings ending with "end", and '%middle%' finds strings containing "middle" anywhere
• _: Matches exactly one character, useful when you know the length or need to skip one position

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions LIKE 'DIAB1%' OR conditions LIKE '% DIAB1%';

**Case sensitivity considerations:**By default, LIKE comparisons are case-sensitive depending on your collasion settings. For case-insensitive matching, you can specify a collation explicitly:

WHERE column_name COLLATE utf8_general_ci LIKE 'pattern'

Choosing Between REGEXP and LIKE

Select REGEXP when you need complex pattern matching involving character classes, quantifiers, alternation, or positional anchors. Choose LIKE for straightforward prefix, suffix, or substring matching where performance matters and the pattern is simple. REGEXP provides greater power at the cost of slightly slower performance on large datasets.