REGEXP(Text, Regular expression)
Determines if the Text matches the Regular expression.
Text
String
Regular expression
Boolean
A regular expression should be passed as a string. If the regular expression contains a backslash (\), use double backslashes (\\) instead to escape it. For example, the formula REGEXP(String,"\d") is invalid and needs to be changed to REGEXP(String,"\\d"), as shown in the following figure.
If any parameter is NULL, the result is NULL.
REGEXP("aaaaac","a*c")
true
REGEXP("abc","a*c")
false
Examples of common regular expressions:
Checking if a phone number is valid
^1(3\d|5[36789])\d{8}$
A phone number has 11 digits. The first digit is 1, the second digit is 3 or 5. When the second digit is 5, the third digit can only be one of 3, 6, 7, 8, or 9.
Checking if a Chinese name is valid
^[\u4e00-\u9fa5]+$
If a string is not Chinese, or there are spaces before or after a Chinese character, the string is considered invalid.
Checking if HTML tags match
<(.*)>.*<\/\1>|<(.*) \/>
If a <html> tag appears without a corresponding </html>, the HTML tags do not match.
Checking if a URL is valid
[a-zA-z]+://[^\s]*
A valid URL should start with letters followed by a colon and double slashes (://).
Checking if an IP address is valid
(\d+)\.(\d+)\.(\d+)\.(\d+)
A valid IP address is in dot-decimal notation. For example, 192.168.100.11 is valid.
Checking if the account format is valid
^[a-zA-Z][a-zA-Z0-9_]{4,15}$
The account should start with a letter, followed by uppercase letters, lowercase letters, numbers, or underscores, with the length of 5 to16 characters.
Checking if a QQ number is valid
^\s*[0-9]{4,9}\s*$
A QQ number should be numeric, with the length of 5 to 10 digits.
Checking if a postal code is valid
^[1-9]\d{5}$
A postal code cannot start with 0, with the length of 6 digits.
Checking if an ID number is valid
(^\d{14}$)|(^\d{17}([0-9]|X)$)
An ID number should have either 15 or 18 digits. For 15-digit numbers, all characters should be numeric. For 18-digit numbers, the first 17 characters should be numeric, and the last one is a check bit that can be a number or the letter X.
Checking if the time format is valid
^(0\d{1}|1\d{1}|2[0-3]):([0-5]\d{1})$
Time should be in 24-hour format, with the hour preceding the colon (:) ranging from 01 to 23.
Checking if a password is valid
The password should contain uppercase letters, lowercases letters and numbers, with the length of 8 to 10 characters. Special characters are not allowed.
Checking if an email address is valid
Email formats vary greatly and a frequently used regular expression is ^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$.
The mailbox only allows the following characters: English letters, numbers, underscores (_), periods (.), en dash (–), and hyphens (-).If a mailbox allows Chinese characters, English letters, numbers, and English domains only, the regular expression is ^[A-Za-z0-9\u4e00-\u9fa5]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$.
Checking if the input contains spaces
^[^\s]*$
The regular expression can be used to disallow spaces in the input.
Checking if a user password is valid
^[a-zA-Z]\w{5,17}$
The password should start with a letter, and can only contain letters, numbers, and underscores (_), with the length of 6 to 18 characters.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy