How to use MySQL REGEXP
This article mainly introduces "how to use MySQL REGEXP". In daily operation, I believe many people have doubts about how to use MySQL REGEXP. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use MySQL REGEXP". Next, please follow the editor to study!
1. About NULL
The normal comparison operator is used for NULL, and the result returned is NULL.
Mysql > select 0 = null, 1 null, 2 > null, 3
< null, 4 >= null, 5 null | 3
< null | 4 >= null | 5 select 0 is null, null is null
+-+ +
| | 0 is null | null is null | |
+-+ +
| | 0 | 1 |
+-+ +
1 row in set (0.00 sec)
Mysql > select 0 is not null, null is not null
+-+ +
| | 0 is not null | null is not null | |
+-+ +
| | 1 | 0 |
+-+ +
1 row in set (0.00 sec)
Mysql > select 0 null, null null
+-+ +
| | 0 null | null null | |
+-+ +
| | 0 | 1 |
+-+ +
1 row in set (0.00 sec)
In MySQL, NULL is different from a null value.
Mysql > select''IS NULL
+-+
| |''IS NULL |
+-+
| | 0 |
+-+
1 row in set (0.00 sec)
2.REGEXP
The REGEXP operator can perform more complex string comparison operations, mainly through regular expressions. Regular expressions consist of a mixture of standard characters and metacharacters that specifically define matching patterns. The following table lists the metacharacters that are often used in regular expressions:
Metacharacter action
+ matches the values of one or more preceding characters
* match the values of 0 or more preceding characters
? Match the values of 0 or 1 characters before
. Match any character
^ matches the beginning of the string
$matches the end of the string
S matches a single white space character, including tabulation matching newline characters
S matches all characters except white space characters
D matches numbers between 0 and 9
W matches letters, numbers, and descending characters
W matching uses any character that w cannot match
Mysql > select 'google' regexp' go+ogle', 'google' regexp' go*ogle', 'google' reg
Exp 'go?ogle'
+
-+
| | 'google' regexp' go+ogle' | 'google' regexp' go*ogle' | 'google' regexp' go?og |
Le' |
+
-+
| | 1 | 1 |
1 |
+
-+
1 row in set (0.00 sec)
Mysql > select 'google' regexp' go+gle', 'google' regexp' go*gle', 'google' regex
P 'go?gle'
+-
-+
| | 'google' regexp' go+gle' | 'google' regexp' go*gle' | 'google' regexp' go?gle' |
| |
+-
-+
| | 1 | 1 | 0 |
| |
+-
-+
1 row in set (0.00 sec)
Mysql > select 'google' regexp' gooo+gle', 'google' regexp' gooo*gle', 'google' r
Egexp 'gooo?gle'
+-
-+
| | 'google' regexp' gooo+gle' | 'google' regexp' gooo*gle' | 'google' regexp' goo |
Overngle' |
+-
-+
| | 0 | 1 |
1 |
+-
-+
1 row in set (0.00 sec)
Mysql > select 'google' regexp' ^ goo', 'google' regexp' goo$'
+-+-
| | 'google' regexp' ^ goo' | 'google' regexp' goo$' |
+-+-
| | 1 | 0 |
+-+-
1 row in set (0.00 sec)
Mysql > select 'google' regexp' ^ gle', 'google' regexp' gle$'
+-+-
| | 'google' regexp' ^ gle' | 'google' regexp' gle$' |
+-+-
| | 0 | 1 |
+-+-
1 row in set (0.00 sec)
Mysql > select 'fifi' regexp' ^ fi', 'fifi' regexp' fi$', 'fifi' regexp' ^ fi$', 'f
Ifi' regexp'^ fifi$'
+-
-+
| | 'fifi' regexp' ^ fi' | 'fifi' regexp' fi$' | 'fifi' regexp' ^ fi$' | 'fifi' rege |
Xp'^ fifi$' |
+-
-+
| | 1 | 1 | 0 | |
1 |
+-
-+
1 row in set (0.00 sec)
3. System information function
Here are some common system information functions:
User () or system_user () returns the current login user name
Connection_id () returns the connection ID of the current user
Database () returns the current database name
Version () returns the version of the MySQL server
Mysql > select user (), connection_id (), database (), version ()
+-+
| | user () | connection_id () | database () | version () |
+-+
| | root@localhost | 2 | ggyy | 5.1.34-community | |
+-+
1 row in set (0.40 sec)
Undefinedundefinedundefinedundefinedundefined
At this point, the study on "how to use MySQL REGEXP" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!
