Shulou(Shulou.com)06/02 Report--

This article introduces the knowledge of "specific usage of mysql regular expressions". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Retrieve all rows of column prod_name that contain text 1000

SELECT prod_name FROM products WHERE prod_name REGEXP '1000'

ORDER BY prod_name

Why bother to use regular expressions? In the above example, regular expressions do not bring most of the benefits (and possibly performance degradation).

Consider the following example:

SELECT prod_name FROM products WHERE prod_name REGEXP '.000'

ORDER BY prod_name

Perform OR matching

SELECT prod_name FROM products WHERE prod_name REGEXP '1000 | 2000'

ORDER BY prod_name

SELECT prod_name FROM products WHERE prod_name REGEXP'[123] ton'

ORDER BY prod_name

The above regular expression [123] ton is [1 | 2 | 3] ton abbreviation

Matching range

For example, match 0-9

[0123456789] simplify [0-9] [1-3] [6-9] [Amurz] to match any letter

Match special characters

SELECT prod_name FROM products WHERE prod_name REGEXP'.

ORDER BY prod_name

In order to match special characters, you must use\\ as the leading. \-find -,\. It means to find.

Blank metacharacter

\\ f Page feed\\ nLine break\\ r enter\\ t tabulation\ v Longitudinal tabulation

\ match backslash

Matching character class

[: alnum:] any letter and number is the same as [a-zA-Z0-9]

[: alpha:] any character (same as [a-zAZ])

[: blank:] spaces and tabulation are the same as [\ t]

[: cntrl:] ASC | | Control characters (ASC | | 0 to 31 and 127)

[: digit:] any number is the same ([0-9])

[: lower:] any lowercase letter [amurz]

[: print:] any printable character

[: punct:] any character neither in [: alnum:] nor in [: cntrl:]

[: space:] any white space character including spaces

[: upper:] any uppercase letter

[xdigit:] any hexadecimal number is the same as [a-fA-F0-9]

Match multiple instances

* 0 or more matches

+ 1 or more matches (equal to {1,})

? 0 or one match (equal to {0pm 1})

{n} specified number of matches

{n,} not less than the specified number of matches

{nrecom} matching number range (m does not exceed 255)

Here are a few examples:

Enter:

SELECT prod_name FROM products WHEREpro_name GEGXP'(0-9) sticks?\)'

Output prod_name

TNT (1 stick)

TNT (5 stick)

The regular expression\ (0-9) sticks?\) needs to be explained. \ (match (

[0-9] matches any number

Sticks? Match sticks and stick (after s? Make s optionally match 0 or 1 occurrence of any character before it)

\) match) have you noticed? It will be very difficult to match sticks and stick

Here's another example. This time we're going to match the four digits connected together.

SELECT prod_name FROM products WHEREpro_name GEGXP'[[: digit:]] {4}'

{4} requires that the character in front of it (any number) appear four times so match any four digits connected together.

The above regular expression can also be written as follows: [0-9] [0-9] [0-9] [0-9]

The above summary is the text that matches anywhere in a string. To match text in a specific location, you need to use a locator

^ text start

End of $text

[[:]] the end of a word

For example, what if you want to find all the products at the beginning of a number (including the number starting with the decimal point)?

Simple search [0-9]\. Or [: digit:]\. No, because it will find a match anywhere in the text to match.

The solution is to use the locator ^

SELECT prod_name FROM products WHEREpro_name GEGXP'^ [0-9\\.]'

Splicing field

You can use the Concat () function to use

Example:

SELECT Concat (vend_name,' (', vend_country,')') FROM vendors ORDER BY vend_name

Concat () splicing string, that is, connecting multiple strings to form a longer string.

Match deleted data with excess Airbus

SELECT Concat (vend_name,' (', RTrim (vend_country),') FROM vendors ORDER BY vend_name

The Rtrim () function removes all spaces to the right of the value.

Perform arithmetic operations

Mysql operation operator

+ plus

-minus

* multiply

/ divide

Input

SELECTprod_id,quantity,item_price,quantity*item_price AS expanded_price

FROM orderitems WHERE order_num = 2005

Use data processing functions

Most function implementations support the following types of functions

Handle text strings (such as deleting or populating, converting to uppercase or lowercase)

Perform arithmetic operations on numerical data (e. G. return absolute values, perform algebraic operations)

A date and time function that processes date values and takes specific components from these values (for example, returning two date differences to detect date validity)

A system function that returns the special information being used by DBMS (such as returning login information and checking version details)

For example, Rtrim () removes the space to the right of the column value on the right.

Upper () function

Input

SELECT vend_name,Upper (vend_name) ASvend_name_upcase FROM vendors ORDER BY vend_name

Upper () converts text to uppercase.

Commonly used text processing functions

Left () returns the character on the left

Length () returns the length of the string

Locate () finds a substring of the string

Lower () converts the string to lowercase

Ltrim () removes the space to the left of the string

Right () returns the string on the right

Rtrim () removes the space on the right side of the string

Soundex () returns the SOUNDEX value of the string

SubString () returns the character of the string

Upper () converts the string to uppercase

I would like to explain that SOUNDEX is an alphanumeric pattern algorithm for converting any text string into an alphanumeric representation.

Original

SELECT cust_name,cust_contact FROMcustomers WHERE cust_contact = 'Y.Lie'

Here's how to search using the Soundex () function, which matches all contact names that sound like Y.Lie

SELECT cust_name,cust_contact FROMcustomers WHERE Soudex (cust_contact) = Soudex ('Y.Lie')

Date function

Common date and time processing functions

Numerical processing function

So much for the introduction of "specific usage of mysql regular expressions". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.