MySQL - How to determine if a value is numeric

MySQL doesn’t have a built-in function to check if a string value is a valid number or not.

To determine if a string is numeric, you need to write your own solution.

One way to check if a string is numeric is by writing a regular expression using the REGEXP operator.

For simple numeric values, you can use the following snippet:

REGEXP '^[0-9]+$'

The above regular expression will match the numerical value between 0 to 9 from the start of the value until the end.

To test the expression, let’s create a table named Test with just one VARCHAR column with the following query:

CREATE TABLE `Test` (
  `score` varchar(255) DEFAULT NULL
);

INSERT INTO `Test` (`score`)
VALUES
  ('123'),
  ('444'),
  ('34x'),
  ('AB123'),
  ('23.55'),
  ('0.1231');

The table content would look as follows:

+--------+
| score  |
+--------+
| 123    |
| 444    |
| 34x    |
| AB123  |
| 23.55  |
| 0.1231 |
+--------+

Let’s test if the column rows have numeric values with the regular expression:

SELECT score, score REGEXP '^[0-9]+$' AS isNumeric 
  FROM Test;

The returned result set would be as follows:

+--------+-----------+
| score  | isNumeric |
+--------+-----------+
| 123    |         1 |
| 444    |         1 |
| 34x    |         0 |
| AB123  |         0 |
| 23.55  |         0 |
| 0.1231 |         0 |
+--------+-----------+

As you can see, only integer numbers are considered numeric values using the regular expression above.

If you’d like to include floating or decimal numbers, then you need to use the following regex:

REGEXP '^[0-9]+\\.?[0-9]*$'

The above regex will match the trailing decimal numbers after the dot (.) symbol.

Let’s test it again with the same data:

SELECT score, score REGEXP '^[0-9]+\\.?[0-9]*$' AS isNumeric 
  FROM Test;

The output of the query above will be as shown below:

+--------+-----------+
| score  | isNumeric |
+--------+-----------+
| 123    |         1 |
| 444    |         1 |
| 34x    |         0 |
| AB123  |         0 |
| 23.55  |         1 |
| 0.1231 |         1 |
+--------+-----------+

Finally, you can create a custom function in your MySQL database server called isNumeric() with the following definition:

CREATE FUNCTION isNumeric (
  input VARCHAR(255)
)
RETURNS INT DETERMINISTIC
RETURN input REGEXP '^[0-9]+\\.?[0-9]*$';

The function isNumeric() above accepts one VARCHAR parameter so that you can pass a VARCHAR column to the function.

Each time you need to find out if a value is numeric, you just need to call the function like this:

SELECT score, isNumeric(score) FROM Test;

-- +--------+------------------+
-- | score  | isNumeric(score) |
-- +--------+------------------+
-- | 123    |                1 |
-- | 444    |                1 |
-- | 34x    |                0 |
-- | AB123  |                0 |
-- | 23.55  |                1 |
-- | 0.1231 |                1 |
-- +--------+------------------+

By creating a custom isNumeric() function, you don’t need to write the regular expression each time you need to determine if a string is numeric.

Feel free to modify the code above to fit your project 😉