MySQL fuzzy query: like mode and RegExp mode

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.