How to Extract Only the Digits from a String in MySQL

To mysql extract digits from string — pulling 123456 out of abc123def456 — use REGEXP_REPLACE with the [^0-9] class in a SELECT. The class negation strips everything that isn’t a digit, leaving only the numeric characters. This guide covers the basic select, the preserve-sign-and-decimal variant, an UPDATE that writes the cleaned value back, and generated-column caching for hot paths.

Last verified: 2026-04-23 on MySQL 8.0 and MariaDB 10.11. Originally published 2022-12-20, rewritten and updated 2026-04-23.

TL;DR

SELECT REGEXP_REPLACE(string_column, '[^0-9]', '') AS digits
FROM table_name;

How the regex works

• [0-9] — matches any single digit.
• [^0-9] — matches any character that is not a digit (the ^ at the start of a character class negates it).
• REGEXP_REPLACE(str, pattern, replacement) — replaces every pattern match with the replacement. Passing an empty string as replacement deletes those characters.

Put together: every non-digit character is removed. "abc123def456" → "123456".

Preserving minus sign and decimal point

-- "USD -12.50" -> "-12.50"
SELECT REGEXP_REPLACE(string_column, '[^-0-9.]', '') AS numeric_value
FROM table_name;

-- Cast to decimal for math
SELECT CAST( REGEXP_REPLACE(string_column, '[^-0-9.]', '') AS DECIMAL(10, 2) ) AS value
FROM table_name;

For currency-string cleanup (see also PHP string-to-float for the same pattern server-side), include - and . in the keep-set. The final CAST turns the cleaned string into a proper decimal you can use in WHERE or SUM.

Updating the column in place

-- Test on a small slice first
UPDATE table_name
SET string_column = REGEXP_REPLACE(string_column, '[^0-9]', '')
WHERE id < 100;

-- Then run the full update
UPDATE table_name
SET string_column = REGEXP_REPLACE(string_column, '[^0-9]', '');

Destructive — once you’ve replaced "abc123" with "123" the original letters are gone. Take a backup first, and run against a WHERE id < 100 sample to confirm the transform looks correct before touching the whole table.

Generated column (MySQL 8.0+)

ALTER TABLE table_name
ADD COLUMN digits_only VARCHAR(255)
    GENERATED ALWAYS AS (REGEXP_REPLACE(string_column, '[^0-9]', ''))
    STORED;

-- Query the pre-computed column
SELECT digits_only FROM table_name WHERE id = 42;

When you need the extracted digits repeatedly (filtering, joining, indexing), a STORED generated column computes it once on write and caches the result. REGEXP_REPLACE runs only on INSERT / UPDATE, not on every SELECT. You can also add an index on digits_only to make phone-number or invoice-number lookups fast.

MySQL 5.7 and earlier

REGEXP_REPLACE landed in MySQL 8.0. On 5.7 the best options are:

• Upgrade — MySQL 5.7 reached end-of-life in October 2023 and no longer gets security updates. If you’re still on it, the upgrade path is the right answer.
• Do it in the app layer — one-liner in any language. PHP: preg_replace('/\D+/', '', $value). Python: re.sub(r'\D', '', value). Adds a round trip but works on any MySQL version.
• User-defined function — write a stored function that loops through the string character-by-character. Works, but slow and awkward.

Frequently asked questions

Related guides

• How to Remove a Specific String from a Column in MySQL — the sibling REPLACE / REGEXP_REPLACE pattern.
• How to Combine Multiple Columns as One String in MySQL — composite string operations.
• How to Convert a String to Float in PHP — the app-layer equivalent for currency strings.
• How to Fix “Unknown column ‘CONCAT'” in Laravel — using MySQL string functions from Eloquent.

References

MySQL REGEXP_REPLACE docs: dev.mysql.com/doc/refman/8.0/en/regexp.