MySQL fuzzy query: like mode and RegExp mode

MySQL fuzzy query provides two modes: like mode and RegExp mode.

Like mode

The like pattern is a fuzzy query using a like or not-like comparison operator.

SELECT  from WHERE  like [not like] Conditions

For a condition, there are several wildcard characters:

Wildcard characters	Meaning
%	Represents any one or more characters that can match any type and length of character
_	Represents any single character, matching a single arbitrary character
ESCAPE	Keyword to define an escape character. In a pattern, the wildcard is interpreted as a normal character when the escape character is placed before the wildcard character.

Example:

# Select a person from the "Persons" table that lives in a city that starts with "Ne"SELECT *  fromPersonsWHERECity like 'ne%'# Select a person living in a city containing "Lond" from the "Persons" tableSELECT *  fromPersonsWHERECity like '%lond%'# "Eorge" after the first character of the name is selected from the "Persons" tableSELECT *  fromPersonsWHEREFirstName like '_eorge'# The last name of the record selected from the "Persons" table starts with "C", then an arbitrary character, then "R", then any character, then "ER"SELECT *  fromPersonsWHERELastName like 'C_r_er'# Find the calculation process from the KPIs table that contains 0%the indicatorSELECT *  fromKpiWHERECount_process like '%0/%%' ESCAPE '/'

Attention:

1. Wildcard characters must be used with the LIKE operator
2. If wildcard characters are not used, like and = are equivalent

RegExp mode

The REGEXP pattern is a regular expression matching query using the REGEXP operator.

SELECT  from WHERE ' conditions '

For a condition, there are several wildcard characters:

Wildcard characters	Meaning
^	Matches the starting position of the input string. If the Multiline property of the RegExp object is set, ^ also matches the position after ' \ n ' or ' \ R '.
$	Matches the end position of the input string. If the Multiline property of the RegExp object is set, $ also matches the position before ' \ n ' or ' \ R '.
.	Matches any single character except "\ n". To match any character including ' \ n ', use a pattern like ' [. \ n] '.
[...]	The character set is combined. Matches any one of the characters contained. For example, ' [ABC] ' can match ' a ' in ' plain '.
[^...]	Negative character set. Matches any character that is not contained. For example, ' [^ABC] ' can match ' P ' in ' plain '.
P1|p2|p3	Match P1 or P2 or p3. For example, ' Z|food ' can match "z" or "food". ' (z|f) Ood ' matches "Zood" or "food".
*	Matches the preceding subexpression 0 or more times. For example, zo* can match "z" and "Zoo". * Equivalent to {0,}.
+	Matches the preceding subexpression one or more times. For example, ' zo+ ' can match "Zo" and "Zoo", but not "Z". + equivalent to {1,}.
N	N is a non-negative integer. Matches the determined n times. For example, ' o{2} ' cannot match ' o ' in ' Bob ', but can match two o in ' food '.
{N,m}	Both M and n are non-negative integers, where n <= m. Matches at least n times and matches up to M times.

Example:

# Look in the Name field to'St'for all data starting with:SELECTName fromPerson_tblWHEREName REGEXP'^st'; # Look in the Name field to'OK'all data for the end:SELECTName fromPerson_tblWHEREName REGEXP'ok$'; # Find the Name field contains'Mar'all the data for the string:SELECTName fromPerson_tblWHEREName REGEXP'Mar'# Find the Name field that starts with a vowel character and'OK'all data at the end of the string:SELECTName fromPerson_tblWHEREName REGEXP'^[aeiou]|ok$';

MySQL fuzzy query: like mode and RegExp mode