MySQL pattern matching two methods

• I. Using a like or not-like comparison operator

Use "_" to match any single character, while "%" matches any number of characters (including 0 characters);

For example:

1. To find out which name begins with "B":

MySQL>SELECT*fromWHEREto like'b% ';

2. To find out “fy” the name with the end:

MySQL>SELECT*fromWHEREto like'%fy' ;

3. To find out “w” which name to include:

MySQL>SELECT*fromWHERElike' %w% ';

4. To find a name that contains exactly 5 characters, use the “_” pattern character:

MySQL>SELECT*fromWHEREto like'_____ ';

• Second, using the regexp and not regexp operators

⑴ "." matches any single character;

The ⑵ character class “[...]” matches any character within the square brackets. For example, “[abc]” match “a” , “b” or “c” . To name a range of characters, use a "-". “[a-z]”matches any letter, and “[0-9]” matches any number.

⑶ “ * ”Match 0 or more characters in front of it. For example, match any number of “x*” “x” characters, match any number of “[0-9]*” numbers, and “.*” match any number of characters.

⑷ to locate a pattern so that it must match the beginning or end of the value being tested, or at the beginning of the pattern “^” 在模式的结尾用“$” .

1. In order to find the first “b” name, use the beginning of the “^” matching name:

MySQL>SELECT*fromWHERE'^b';

2. If you want to force regexp to be case-sensitive, use the binary keyword to make one of the strings into a binary string. The query matches only the lowercase ' b ' of the first letter of the name:

MySQL>SELECT*fromWHEREBINARY'^b  ';

3. In order to find with the “fy” end of the name, use the end of the “$” matching name:

MySQL>SELECT*fromWHERE'fy$ ';

4. To find “w” the name that contains one, use the following query:

MySQL>SELECT*fromWHERE'w';

5. In order to find the name containing exactly 5 characters, use “^” and “$” match the start and end of the name, and 5 “.” instances between the two:

MySQL>SELECT*fromWHERE'^.....$ ';

You can also “{n}” rewrite the previous query using the repeat N times operator:

MySQL>SELECT*fromWHERE'^.{ 5}$';

• Difference

1. like matches the value of the entire field, for example, do not use "_" or "%" words, use the above SQL query contains a "w"  name, such as "w" is not found, and regexp can;
2. Like can only use "_" and "%", and regular expressions are not supported.

MySQL pattern matching two methods