How to determine whether mysql is a number

In mysql, the REGEXP operator can be used in conjunction with the regular expression "[^0-9.]" to determine whether the data is a number. The syntax is "field REGEXP '[^0-9.]'" ; When the result of the statement is "1", you can filter the data that is not a number. When the result of the statement is "0", you can filter the data whose value is a number.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How does mysql determine whether it is a number

Most numbers are stored in int or bigint, but some fields use strings to store numbers, so when we need to determine whether the string format is When it is all numbers, you will encounter this problem.

So, how to judge?

Method

Use mysql's REGEXP operator

{String} REGEXP '[^0-9.]'

The first string is what we need to judge, and the following string is mysql's Regular expression, meaning to match characters that are not numbers or decimal points.

If the String contains a number that is not between 0-9 or a decimal point, it returns true, otherwise it returns false.

Usage

select ('123a' REGEXP '[^0-9.]');

--'123a' contains the character 'a', the output result is 1, the output of constant true in mysql is 1, the output of false is 0

select * from tablename where (name REGEXP '[^0-9.]') = 1

Query records whose names are all numbers

Note: If there are spaces in the string, the regular expression will also be matched and 1 will be returned. If you want to remove the spaces at both ends, you need to use the trim() function on the string you are judging.

#Query the data whose speed column is not a number

select * from standard_csbi_service_tree_1d_full where (‘2134’ REGEXP ‘[^0-9.]’)=1;

#Query the data whose speed column is a number

select * from standard_csbi_service_tree_1d_full where (speed REGEXP ‘[^0-9.]’)=0;

Recommended learning: mysql video tutorial

The above is the detailed content of How to determine whether mysql is a number. For more information, please follow other related articles on the PHP Chinese website!