Introduction to Regular Expressions in Mysql

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.

 

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:

　　　　TRUNCATE(N, D);


Example. 

Answer to the question.

select  name  from students where marks > 75 order by substring(name,-3,3) , id asc

key: SUBSTRING(string, start, length),
　　if start = 1, then it means start from the left first character,

　　if starte = -1, then it means start from the right first charcter.