📝 [SQL] Regular Expressions (Regex)

💡 What is Regex?

Regular expressions, commonly known as regex or regexp, are sequences of characters that define a search pattern.
These patterns are used for string matching, searching, and replacing perations.
In SQL, regex can be utilized to perform complex string manipulations that go beyond basic SQL string functions

💡 Key Concepts of Regular Expressions

1. Basic Syntax

• Literal Matching: Matches the exact characters in the string
• Metacharcters : Characters with special meanings, such as . for any character, ^ for the start of a string, and $ for the end of a string.
• Quantifiers: Define the number of instances for a match.
  • : 0 or more occurrences
  • + : 1 or more occurrences
  • {n} : Exactly n occurrences
  • {n, } : n or more occurrences
  • {n,m} : Between n and m occurrences
• Character Classes : Define a set of characters
  • [abc] : Matches any one of a, b, or c
  • [^abc] : Matches any character except a, b, or c
  • [a-z] : Matches any lowercase letter
  • [0-9] : Matches any digit
• Groups and Alternation : Grouping parts of the regex and providing alternatives.
  • [abc] : Matches the exact sequence "abc"
  • a|b : Matches either a or b

2. Advanced Techniques

• Lookahead and Lookbehind : Zero-width assertions that specify a pattern must be preceded or followed by another pattern
  • (?=abc) : Positive lookahead
  • (?!abc) : Negative lookahead
  • (?<=abc) : Positive lookbehind
  • (?<!abc) : Negative lookbehind

3. Common Uses in SQL

• String Matching : Finding patterns within strings
• Validation : Ensuring data conforms to a specified format (e.g., email addresses, phone numbers)
  -Extraction : Pulling out specific portions of a string
  -Replacement : Replacing portions of a string with other values.

---

💡 Practical Examples in SQL

Using REGEXP in MySQL

• Pattern Matching

SELECT * FROM employees WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

This query selects all employees with valid email addresses

• Finding Specific Formats

SELECT * FROM orders WHERE order_code REGEXP '^ORD[0-9]{3}$';

This query selects all orders with codes that start with "ORD" followed by exactly three digits