Use of wildcard characters in MySQL queries
Wildcard characters are often used in MySQL queries, and there is a difference between MySQL's wildcard characters and Pgsql (discussed later), and regular expressions can be used in MySQL.
SQL Pattern Matching:
"_" matches a single character, "\_" matches "_"
"%" matches any character, including 0 characters
In SQL mode, the default is case-insensitive, and fuzzy matches in SQL mode cannot use "=" or "! =" and use like or notlike.
For example:
SELECT * FROM [user] WHERE u_name like '% three ';
SELECT * FROM [user] WHERE u_name like ' _ three _ ';
Regular pattern matching:
when using regular matches, use the regexp and not regexp operators (or Rlike and notrlike, which are synonyms).
The characters involved are:
"." matches any single character.
"[...]" matches any character within square brackets. For example, "[ABC]" matches "a", "B" or "C", "[A-z]" matches any lowercase letter, and "[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.
For example:
– Search for names starting with three
From [user] WHERE u_name REGEXP ' ^ three ';
– Search with the name of the three endings
From [user] WHERE u_name REGEXP ' three $ ';
– the "Repeat n Times" operator overrides the previous query:
From [user] WHERE u_name REGEXP ' b{2}$ ';
Use of wildcard characters in MySQL queries
