| |
Fuzzy query MySQL offers two modes: LIKE and REGEXP pattern mode.
LIKE pattern
LIKE pattern is LIKE or NOT LIKE comparison operators using fuzzy query.
SELECT fields FROM table WHERE field LIKE [NOT LIKE] 'condition'
For conditions, the following wildcards:
Wildcard Meaning
% Represents any one or more characters, a character can match any type and length
_ Represents any single character matches any single character
ESCAPE keyword defines the escape character. In mode, when the escape character is placed before a wildcard, the wildcard is interpreted as ordinary characters.
Example:
# Choose to live in "Ne" the beginning of the city from the "Persons" table people
SELECT * FROM Persons WHERE City LIKE 'Ne%'
# Selected people live in contains "lond" city from the "Persons" table
SELECT * FROM Persons WHERE City LIKE '% lond%'
# Select the name from "Persons" table after the first character is "eorge" people
SELECT * FROM Persons WHERE FirstName LIKE '_eorge'
# Selected from the "Persons" table Surname this record to "C" at the beginning, then any character, then "r", then any character, then "er"
SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'
# Find "KPI" calculation table containing an index of 0%
SELECT * FROM KPI WHERE COUNT_PROCESS LIKE '% 0 / %%' ESCAPE '/'
note:
Wildcards must be used with the LIKE operator
If you do not use a wildcard, LIKE and = are equivalent
REGEXP pattern
REGEXP use REGEXP operator mode is for regular expression matching the query.
SELECT fields FROM table WHERE field REGEXP 'condition'
For conditions, the following wildcards:
Wildcard Meaning
^ Matches the beginning of the string. If you set the Multiline property of the RegExp object, ^ also matches '\ n' position or '\ r' after.
$ Matches the input end of the string. If you set the Multiline property RegExp object, also matching $ '\ n' or '\ r' position before.
Match except "\ n" any single character. To match including '\ n', including any characters, like the use of '[. \ N]' mode.
[...] Set of characters. Matches any character included. For example, '[abc]' matches "plain" in the 'a'.
[^ ...] Negative character set. Matches any character that is not included. For example, '[^ abc]' matches "plain" in the 'p'.
p1 | p2 | p3 match p1, p2, or p3. For example, 'z | food' can match the "z" or "food". '(Z | f) ood' will match "zood" or "food".
* Matches the preceding subexpression zero or more times. For example, zo * can match the "z" and "zoo". * Equivalent to {0,}.
+ Match the preceding subexpression one or more times. For example, 'zo +' will match "zo" and "zoo", but can not match the "z". + Is equivalent to {1}.
{N} n is a non-negative integer. Matching the determined n times. For example, 'o {2}' does not match the "Bob" in the 'o', but can match the "food" in the two o.
{N, m} m and n are non-negative integers, where n <= m. Match at least n times and match up to m times.
Example:
# Find the name field, all data 'st' beginning with:
SELECT name FROM person_tbl WHERE name REGEXP '^ st';
# Find the name field, all data is 'ok' for the ending:
SELECT name FROM person_tbl WHERE name REGEXP 'ok $';
# Find the name field contains 'mar' string of all data:
SELECT name FROM person_tbl WHERE name REGEXP 'mar';
# Find the name field to begin with vowels and with 'ok' end of the string of all data:
SELECT name FROM person_tbl WHERE name REGEXP '^ [aeiou] | ok $'; |
|
|
|