Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to judge whether mysql is a number". In the operation of actual cases, many people will encounter such a dilemma. Next, 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!

In mysql, you can use the REGEXP operator and the regular expression "[^ 0-9.]" When used to determine whether the data is a number, the syntax is "field REGEXP'[^ 0-9.]'"; when the statement result is "1", you can filter the data that is not a number, and when the statement result is "0", 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 tell if it's a number?

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

So, how to judge?

Method

Using the REGEXP operator of mysql

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

The first string is what we want to judge, and the following string is the regular expression of mysql, which means to match characters that are not numbers or decimal points.

Returns true if the String contains a number other than 0-9 or a decimal point, and vice versa, returns false.

Usage

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

-- '123a' contains the character' a'. The output result is 1 mysql. The constant true output is 1 false output is 0.

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

Query records whose name are all numbers

Note: if there is a space in the string, it will also match to the regular expression and return 1. If you want to remove the spaces at both ends, you will have to use the trim () function on the judged string.

# query data where the speed column is not a number

Select * from standard_csbi_service_tree_1d_full where ('2134' REGEXP' [^ 0-9.]') = 1

# query data where the speed column is a number

Select * from standard_csbi_service_tree_1d_full where (speed REGEXP'[^ 0-9.]') = 0; that's all for "how mysql can tell if it's a number". 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.