mysql Fuzzy Query like and REGEXP

Preface

Fuzzy queries implemented in mysql are like and regexp.This article gives you a detailed description of how to use the two through the example code, and the following will follow this site 1 to learn.

like mode

like means to look alike, with two modes: _And%

_Represents a single character, usually used to query fixed-length data, such as finding out the names of all three characters of the King, assuming the name column is name, note that there are two following "King"_

select name from  Table Name  where name like ' king __';

% denotes zero or more arbitrary characters, such as finding out the names of all kings

select name from  Table Name  where name like ' king %';

Find out all names that contain the word "Hua"

select name from  Table Name  where name like '% China %';

Regular mode

^, matching the starting position of the string, or the example above, querying all the names of Kings

select name from  Table Name  where name regexp '^ king ';

$, matches the end of the string, such as querying for all names with "clear" names at the end

select name from  Table Name  where name regexp ' bright $';

Matches any single character except \n, similar to,No more sql statements

[...], match any one of the characters contained in [], abcdef...xyz can be abbreviated as [a-z], 0123456789 abbreviated as [0-9], for example, query the person whose name begins with w/z/s

select name from  Table Name  where name regexp '^[wzs]';

[^...], matches characters not included in [], such as querying for names other than the beginning of w/z/s

select name from  Table Name  where name regexp '^[^wzs]';

a | b | c, matches a or b or c, if the employee whose performance is A-or A or A+is identified, assume the performance column is performance

select performance from  Table Name  where performance regexp 'A-|A|A+';

*, repeat 0 or more times, as everyone familiar with the javascript rule knows

'str*'matches st/str/strr/strrr...

?,Repeat 0 or 1 times

'str ?'Can match st/str

+, repeat one or more times

'str+'matches str/strr/strrr/strrrr...

Compared with the regular in javascript, the regular here is a simplified version, with no lazy/greedy matching, [] does not support the grammar\wsd, nor does it support Chinese, which is relatively simple.

One thing to note is that these two modes should not be mixed. The like mode does not support regular expressions and the REGEXP mode does not recognize _And%

summary

This is the whole content of this article. I hope that the content of this article can help you to learn or work. If you have questions, you can leave a message to exchange.