Introduction

MySQL does not provide a built-in ISNUMERIC() function like some other database systems. If you need to detect whether a value is numeric, the right approach depends on what the column actually stores and how strict your definition of "number" needs to be.

If the column already uses a numeric type, schema design has already answered the question. If the column is text and may contain numeric-looking strings, regular expressions are usually the safest way to validate them.

Use The Schema First

If a column is declared as INT, DECIMAL, or another numeric type, then MySQL already enforces numeric storage rules. In that case, a runtime numeric check is usually unnecessary. The more interesting case is a VARCHAR or TEXT column that contains mixed values such as 123, 45.6, abc, and 12x.

For text columns, avoid relying on implicit numeric conversion alone. MySQL is permissive and may coerce partially numeric strings in ways that surprise you.

Detect Numbers With A Regular Expression

A regex check is the clearest way to define what counts as numeric. For integers and decimals with an optional leading minus sign, a common pattern is:

SELECT value,
       value REGEXP '^-?[0-9]+(\\.[0-9]+)?$' AS is_numeric
FROM sample_data;

This pattern matches values such as 42, -7, and 3.14. It does not match 12abc, 1.2.3, or an empty string.

If you are on a MySQL version that supports REGEXP_LIKE, the same intent can be written more explicitly:

SELECT value,
       REGEXP_LIKE(value, '^-?[0-9]+(\\.[0-9]+)?$') AS is_numeric
FROM sample_data;

That makes the validation rule visible and easy to modify.

Be Clear About What "Number" Means

Numeric validation often depends on the domain. Do you want to allow leading plus signs, scientific notation, or surrounding whitespace? Different rules need different patterns.

For example, if you also want to allow a leading plus sign:

SELECT value,
       value REGEXP '^[+-]?[0-9]+(\\.[0-9]+)?$' AS is_numeric
FROM sample_data;

If you need scientific notation such as 1e6, your regex must say so explicitly. There is no single universal numeric definition that fits every application.

Why Simple Casting Can Mislead You

A tempting shortcut is to cast the value and compare the result, but MySQL's coercion rules can accept partially numeric strings:

SELECT CAST('12abc' AS DECIMAL(10,2));

That does not prove the original string was a clean number. It only proves MySQL could coerce part of it into one. That distinction matters in validation code.

If your goal is strict validation, regex is usually safer than coercion. If your goal is best-effort parsing, then casting may be acceptable, but that is a different requirement.

Use A Helper Expression In Queries

In reporting or cleanup jobs, it is often useful to classify rows directly:

1SELECT
2    value,
3    CASE
4        WHEN value REGEXP '^-?[0-9]+(\\.[0-9]+)?$' THEN 'numeric'
5        ELSE 'not numeric'
6    END AS classification
7FROM sample_data;

This is a practical way to find bad rows before migrating a text column into a numeric type.

Common Pitfalls

The most common mistake is looking for a MySQL equivalent of SQL Server's ISNUMERIC() and assuming one exists. It does not. Another problem is using implicit conversion and thinking '12abc' should count as non-numeric when MySQL quietly coerces it in arithmetic contexts. Developers also often forget to define the acceptable numeric format precisely. Integers, decimals, signed numbers, and scientific notation are related but different validation rules. Finally, if the column should only ever store numbers, the better long-term fix is often to change the schema instead of repeatedly validating a text field at query time.

Summary

• MySQL does not have a built-in ISNUMERIC() function.
• For text columns, regex is usually the safest way to test whether a value is numeric.
• Write the regex to match the exact numeric formats your application allows.
• Do not rely on implicit casts for strict validation because MySQL is permissive.
• If the data is truly numeric by design, use a numeric column type instead of a text column.