Mysql Regular Expression queries records that contain non-numbers and characters, mysql Regular Expression

Mysql Regular Expression queries records that contain non-numbers and characters, mysql Regular Expression

For example, we have a school table with the county_name field. Now we want to query records whose county_name field contains characters other than a-w letters and numbers. How can we write SQL statements? See the following statement:

select * from info where name regexp '[^a-w0-9]'; 

The Regular Expression in mysql uses the regexp keyword, [^ a-w0-9], to match characters other than a-w letters and numbers.

This section describes other mysql Regular Expressions:

Match All rows whose names contain 1000

SELECT * FROM a1 WHERE name regexp '1000'

Match All rows ending with 000 (. Regular Expression: match any character)

SELECT * FROM a1 WHERE name regexp '.000'

Mysql will match the regular case. binary keywords can be used for Case sensitivity, for example:

SELECT * FROM a1 WHERE name LIKE binary '% J %' # use LIKE + wildcard to match uppercase JSELECT * FROM a1 WHERE name regexp binary 'J' # use regular expression to match lowercase j

| It is the OR operator of the regular expression, indicating matching one of them

SELECT * FROM a1 WHERE name regexp binary 'a|j|G' 

Match a specific character. If you use a [] character, it will match any single character.

SELECT * FROM a1 WHERE name regexp '[12]st'

The above '[12] st' regular expression, [12] defines a group of characters, which means matching 1 or 2

^ Starting from matching characters

Select * from info where name regexp '^ l'; // query records starting with L in the name field of the info table

$ End part of matching character

Select * from info where name regexp 'C $ '; // query records ending with c from the name field of the info table

. Match any character in the string, including carriage return and line feed

Select * from info where name regexp '^ L .. y $'; // query records starting with L and ending with y with two arbitrary characters in the name field of the info table

[Character Set combination] match any character in the character set

Select * from info where name regexp '[ceo]'; // query any of the three letters c, e, and o in the name field of the info table

[^ Character Set combination] match any character except Character Set combination

Select * from info where name regexp '[^ a-w0-9]'; // query records that contain characters other than a-w letters and numbers from the name field of the info table

S1 | s2 | s3 matches any one of s1s2s3

Select * from info where name regexp 'ic'; // query records containing 'ic' from the name field of the info table.

* Represents multiple characters before the character, including 0 or 1

Select * from info where name regexp 'a * C'; from the name field of the info table, query records with a before c.

+ Represents multiple characters before this character, including one

Select * from info where name regexp 'a + C'; // query the records of a before c in the name field of the info table

String {N} appears N times

Select * from info where name regexp 'a {3} '; // query records that have been a3 times in the name field of the info table

The string {M, N} appears at least M times and at most N times

Select * from info where name regexp 'AB {1, 3}'; // query the name field of the info table for records with a minimum of 1 to 3 times

In MYSQL, the wildcard (LIKE keyword), % can represent any length of characters (including 0), and-can represent a single character

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!