Introduction to Regular Expressions in Mysql

佚名 6年前 (2019-05-15) 随笔 1434人围观抢沙发百度已收录

A regular expression is a special string that describes a search pattern.

It is a powerful tool that gives us a concise and flexible strings of text e.g. characters, and words based on patterns.

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

For example, we can use regular expressions to search for email, IP address, phone number social security number or anything that has a specific pattern.

The advantage of using regular expression is that you are not limited to search for a string based on a fixed pattern with the percent sign (%) and underscore (_) in the LIKE operator. The regular expressions have more meta-characters to construct flexible patterns.

The following illustrates the syntax of the REGEXP operator in the WHERE clause:

SELECT 
    column_list
FROM
    table_name
WHERE
    string_column REGEXP pattern;

If a value in the string_column matches the pattern, the expression in the WHERE clause returns true, otherwise it returns false.

If either string_column or pattern is NULL, the result is NULL.

In addition to the REGEXP operator, you can use the RLIKE operator, which is the synonym of the REGEXP operator.

Examples 1 , Start with.

Code for the answer:

SELECT distinct
    City
FROM
    Station
WHERE
    City REGEXP '^(a|e|i|o|u)'

Example 2. End with

Code Answer

SELECT distinct
    City
FROM
    station
WHERE
    city REGEXP '[a|e|i|o|u]$'

Example 3 . Begin and End with.

Answer code:
. means : matches any single character

* means : matches the preceding character zero or more times

SELECT distinct
    City
FROM
    station
WHERE
    city REGEXP '^(a|e|i|o|u).*[a|e|i|o|u]$'

Round a value to a scale of decimal places.

SELECT ROUND(135.375, 2);

Truncate()

　　　　MySQL TRUNCATE() returns a number after truncated to certain decimal places. The number and the number of decimal places are specified as arguments of the TRUNCATE function.

　　Syntax: