SoFunction
Updated on 2025-05-17

Common example code for string regular expressions in SQL

Basic concepts of regular expressions

Regular Expression (regular expression, abbreviated asregexorregexp) is a pattern description tool for matching character combinations in a string. It defines a conventional rule through a series of special characters and symbols to find, match, replace or verify specific content in a string. Shines in form submission verification, routing mapping, text processing, etc. Basically all programming languages ​​have a specific set of regular mechanisms for programmers to use.

Basic characters of regular expressions

Regular expressions are composed of ordinary characters and special symbols, and can be divided into two major categories. First of allMetacharacter, consists of special symbols, representing the matching criteria in regular matching.Escape charactersUsually by special symbols\+ characters are a general term for a certain character expression type, when the matching content itself contains\When adding another escape, for example\\。

Metacharacter

  • .: Match anyoneCharacters (except newlines).
  • *:matchZero or multiple timescharacters or sub-patterns.
  • +:matchOnce or morecharacters or sub-patterns.
  • ?:matchZero or oncecharacters or sub-patterns.
  • {n}:matchRepeat n timescharacters or sub-patterns.
  • {n,}:matchRepeat ≥n timescharacters or sub-patterns.
  • {n,m}:matchRepeat n to m timescharacters or sub-patterns.
  • []: Match any bracketsonecharacter.
  • [^]:matchNoAnything in square bracketsonecharacter.
  • ^: means that the matching string begins with...
  • $: means that the matching string ends with...
  • (): Grouping, used to disassemble matching content.
  • |: Logical or, means matching any of the multiple patterns.
  • -: When it is not there[]Inside, it represents characters-,exist[]Inside representation range, e.g.[a-z]Indicates any lowercase character.

Escape characters

  • \d: Match anynumberCharacters (0-9).
  • \w: Match anyLetters or numbers or underlinesCharacters (equivalent to[a-zA-Z0-9_]）。
  • \s: Match anyblankCharacters (spaces, tabs, line breaks, etc.).
  • \b: Match the beginning and end of a word, e.g.\bword\bCan match the entire word in a stringwordwithout confusing other inclusionswordcontent.
  • \D: Match anyNon-digitalcharacter.
  • \W: Match anyNon-letters, numbers, underlinesCharacters (equivalent to[^a-zA-Z0-9_]）。
  • \S: Match anyNon-blankcharacter.

Common regular examples

  • Match email address
\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b

Explanation: The match begins with letters, numbers, dots, underscores, etc. (can appear once or more times), followed by@, and then escaped., ending with a domain name and a top-level domain name (letter and at least two characters).

  • Match date
\d{4}-\d{2}-\d{2}

Explanation: YYYY-MM-DD, match four numbers, follow-, followed by two numbers, then followed by-, and finally there are two numbers.

  • Match mobile phone number
^1[3-9]\d{9}$

Explanation:1At the beginning, the second position is3arrive9The number between them is followed by a nine-digit number.

  • Match web elements
<[^>]+>(.*)</[^>]+>

Explanation: <xxx>…</xxx>.

Applying regular expressions in SQL

String search

In SQL, if you want to apply regular expressions to complete the string search function, filter out records that meet the conditions:

SELECT *
FROM Table name
WHERE String fields REGEXP 'regular expression';

Note: Only string fields are supportedREGEXP+Regular statements for search function.

String replacement

When applying regular expressions in SQL to complete the string replacement function:

SELECT regexp_replace(str, pattern, replace_str, occurrence)
FROM Table name;

in:

  • str: A string expression that indicates that regular replacement is required;
  • pattern: Represents regular expressions, and need to be in English quotation marks;
  • replace_str: Represents a new string expression that needs to be replaced after matching the style;
  • occurrence: The value is an integer constant, indicating the degree of replacement. When this value is 0, all matching substrings that meet the regular conditions are replaced. When it is greater than 0, it means that the number of matching substrings that meet the regular conditions is replaced.

Common cases

Example:Find users with valid emails

A valid email has a prefix name and a domain where:

  • The prefix name is a string that can contain letters (upper case or lower case), numbers, and underscores_,point.and/Or dash- ；
  • The prefix name must begin with a letter;
  • The domain name is@ 。

Code:

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

Summarize

This is the article about common example codes for string regular expressions in SQL. For more related SQL string regular expression content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!