How to determine whether mysql is a numeric type

In mysql, you can use the REGEXP operator to determine whether the data is a numeric type. The syntax is "String REGEXP '[^0-9.]'"; this operator is the abbreviation of regular expression. If the data When the characters contain numbers, the returned result is true, otherwise the returned result is false.

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

How does mysql determine whether it is a numeric type?

Using the REGEXP operator of mysql

The REGEXP operator is the abbreviation of regular expression (regular expression). The regular expression is in It is very powerful when searching for strings. The following is about its application

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

The first string is what we need to judge, and the second string is the regular expression of mysql, which means to match those that are not numbers or decimal points. character.

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 the full name Records that are 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 numeric type. For more information, please follow other related articles on the PHP Chinese website!