SQL pattern Matching

Standard SQL pattern matching allows you to use "_" to match any single character, while "%" matches any number of characters (including 0 characters). In MySQL, the default mode of SQL is case-insensitive. Some examples are shown below. Note that you cannot use = or! = when you use SQL mode, and you use the like or not-like comparison operator.

Extended Regular expression pattern matching

Other types of pattern matching provided by MySQL are the use of extended regular expressions. When you test for this type of pattern, use the regexp and not regexp operators (or rlike and not rlike, which are synonyms).

Some of the characters that extend the regular expression are:

“.” matches any single character.

A character class "[...]" Matches any character within the square brackets. For example, "[ABC]" matches "a", "B", or "C". To name a range of characters, use a "-". "[A-z]" matches any lowercase letter, while "[0-9]" matches any number.

"*" matches 0 or more things in front of it. For example, "x*" matches any number of "X" characters, "[0-9]*" matches any number of numbers, and ". *" matches any number of anything.

Regular expressions are case-sensitive, but if you want to, you can use a character class to match two types of writing. For example, "[AA]" matches lowercase or uppercase "a" and "[A-za-z]" matches any letter of two notation.

If it appears anywhere in the value being tested, the pattern matches (as long as they match the entire value, the SQL pattern matches).

To locate a pattern so that it must match the beginning or end of the value being tested, use "^" at the beginning of the pattern or "$" at the end of the pattern.

To find a name that contains exactly 5 characters, use "^" and "$" to match the first and last names, and 5 "." The instance is between the two:

You can also rewrite the previous query using the "{n}" "Repeat N-times" operator:

Standard SQL pattern matching is the standard of SQL language and can be accepted by other relational database systems. Extended regular expression pattern matching is developed on the basis of UNIX system standards and is generally only available on MySQL, but its functionality is stronger than standard SQL pattern matching.

SQL pattern Matching