MySQL fuzzy query: LIKE and REGEXP Modes

MySQL fuzzy query: LIKE and REGEXP Modes

MySQL fuzzy query provides two modes: LIKE mode and REGEXP mode.

LIKE Mode

The LIKE mode uses the LIKE or not like comparison operator to perform fuzzy queries.

SELECT field FROM table WHERE field LIKE [not like] 'condition'

For conditions, there are the following wildcards:

Wildcard	Description
%	Represents any one or more characters, can match any type and length of Characters
_	Represents any single character, matching a single arbitrary character
ESCAPE	The keyword defines the escape character. In mode, when an escape character is placed before a wildcard, the wildcard is interpreted as a common character.

 Example:

# SELECT * FROM Persons WHERE City LIKE 'ne % 'FROM the "Persons" table for Persons living in the City starting with "Ne" # FROM the "Persons" Table SELECT Persons living in the include" SELECT * FROM Persons WHERE City LIKE '% lond %' # FROM the "Persons" Table SELECT * FROM Persons WHERE FirstName LIKE '_ eorge' # the last name of the record selected FROM the "Persons" table starts with "C, then it is an arbitrary character, followed by "r", followed by any character, then SELECT * from kpi where COUNT_PROCESS LIKE '% 0/% 'escape '/'

Note:

1. Wildcards must be used with the LIKE operator.
2. If no wildcard is used, LIKE and = are equivalent.

REGEXP Mode

The REGEXP mode uses the REGEXP operator to perform regular expression matching queries.

SELECT field FROM table WHERE field REGEXP 'condition'

For conditions, there are the following wildcards:

Wildcard	Description
^	Matches the start position of the input string. If the Multiline attribute of the RegExp object is set, ^ matches the position after '\ n' or' \ R.
$	Matches the end position of the input string. If the Multiline attribute 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.
[...]	Character Set combination. Match any character in it. For example, '[abc]' can match 'A' in "plain '.
[^...]	Negative value character set combination. Match any character not included. For example, '[^ abc]' can match 'p' in "plain '.
P1 | p2 | p3	Match p1, p2, or p3. For example, 'z | food' can match "z" or "food ". '(Z | f) ood' matches "zood" or "food ".
*	Matches the previous subexpression zero or multiple times. For example, zo * can match "z" and "zoo ". * Is equivalent to {0 ,}.
+	Match the previous subexpression once or multiple times. For example, 'Zo + 'can match "zo" and "zoo", but cannot match "z ". + Is equivalent to {1 ,}.
{N}	N is a non-negative integer. Match 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. Match at least n times and at most m times.

Example:

# Search for all data starting with 'st' In the name field: SELECT name FROM person_tbl WHERE name REGEXP '^ st'; # search for all data ending with' OK 'In the name field: SELECT name FROM person_tbl WHERE name REGEXP 'OK $'; # search for all data contained in the 'mar' string in the name field: SELECT name FROM person_tbl WHERE name REGEXP 'mar '; # search for all data starting with a vowel character in the name field and ending with a 'OK' string: SELECT name FROM person_tbl WHERE name REGEXP '^ [aeiou] | OK $ ';

This article permanently updates the link address: