MySQL fuzzy query: LIKE and REGEXP Modes

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

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

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.

'Condition'

For conditions, there are the following wildcards:

Wildcard characters

% Represents any one or more characters that can match any type and length

_ Represents any single character, matching a single arbitrary character

The ESCAPE 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:

# From the "Persons" table, select people who live in the city starting with "Ne" # From the "Persons" table, select people who live in the city containing "lond" # From the first character in the "Persons" table is followed by "eorge" person Persons # the last name of the record selected from the "Persons" table starts with "C, then there is an arbitrary character, followed by "r", followed by any character, followed by "er" Persons # From the "KPI" table, find the KPI with 0% contained in the calculation process

Note:

Wildcards must be used with the LIKE operator.

If no wildcard is used, LIKE and = are equivalent.

REGEXP Mode

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

For conditions, there are the following wildcards:

Wildcard characters

^ 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.

. Match 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 '.

[^...] Combination of negative character sets. Match any character not included. For example, '[^ abc]' can match 'p' in "plain '.

P1 | p2 | p3 matches 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.

Both {n, m} 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 the name field:; # search for all data ending with the name field:; # search for all data containing the string in the name field :; # search for all data starting with a vowel character and ending with a string in the name field :;