Detect if value is number in MySQL

Mastering REGEXP for digits

How do we ensure a column value is a proud citizen of the "digit kingdom"? The '^[0-9]+$' REGEXP. This REGEXP unmasks strings that only contain digits.

-- SQL query giving a 'High Five!' to digits
SELECT value, value REGEXP '^[0-9]+$' AS is_digit FROM your_table;


When numbers get decimal and scientific

What if numbers want to go all decimal or scientific on you? Fear not, REGEXP has your back.

-- SQL query that says, "We speak Decimal & Scientific, bro!"
SELECT value REGEXP '^[0-9]+\\.?[0-9]*$' AS is_numeric FROM your_table;


This REGEXP identifies both integer and decimal numbers, or numbers diving into the deep "scientific notation" sea.

Coercing MySQL into numeric shape

What happens when MySQL stubbornly sees a numeric string as a character type? You pull out your Type Coercion card.

-- SQL query: "When life gives you characters, make numbers!"
SELECT value, concat('', value * 1) = value AS is_numeric FROM your_table;


This coerces character strings into numeric data, especially useful when you need to perform mathematical operations with your data.

Catching numeric patterns in the wild

To catch numbers trying to sneak around in your string values, you can use the REGEXP '[0-9]+'.

-- SQL equivalent of "We see you, sneaky numbers!"
SELECT value, value REGEXP '[0-9]+' AS contains_digit FROM your_table;


This REGEXP gives you a big "gotcha" moment whenever it encounters even a single digit in your strings.

The full REGEXP ensemble for various numeric formats

We've discussed several REGEXP variations, but now let's bring them all together to handle all types of numeric formats.

-- SQL query: "Numeric UNO - All types, just come!" 
SELECT value REGEXP '^[+-]?[0-9]*\.?[0-9]+([eE][+-]?[0-9]+)?$' AS is_numeric FROM your_table;


This pattern advertises an open house for all numeric kinds: plain, decimal, scientific notation, you name it.

Time to filter our SQL queries

Weeding out non-numeric rows

Sometimes, you only want to party with number folks, so you use the ! operator.

-- SQL query: "Numeric people only, please!"
SELECT * FROM your_table WHERE NOT value REGEXP '^[0-9]+$';


This query filters out the "non-numeric" crowd from your SQL party.

Filtering for positive numeric vibes

Want to hang out with only positive numbers? We got you.

-- SQL query: "Positivity rocks! No negatives, please!"
SELECT * FROM your_table WHERE value > 0;


This query leaves "negative Nellies" out in the cold.