Introduction

If the goal is to remove zeros only from the very start of a string, the rule is simple: delete the longest prefix that matches one or more 0 characters, then keep the rest untouched. That works for purely numeric values like 000123 and for alphanumeric values like 000ABC123.

In modern MySQL, the cleanest solution is a regular expression anchored at the beginning of the string. Older versions can still solve it, but the SQL is less elegant.

The Simplest Solution in MySQL 8

MySQL 8 provides REGEXP_REPLACE, which makes this task straightforward.

SELECT REGEXP_REPLACE('000ABC123', '^0+', '') AS trimmed;
SELECT REGEXP_REPLACE('000123', '^0+', '') AS trimmed;
SELECT REGEXP_REPLACE('ABC001', '^0+', '') AS trimmed;

Results:

• '000ABC123 becomes ABC123'
• '000123 becomes 123'
• 'ABC001 stays ABC001'

The pattern ^0+ means “one or more zeros at the start of the string.” Because the expression is anchored with ^, zeros in the middle are left alone.

If you need to apply this to a column, the update is equally direct:

UPDATE part_numbers
SET code = REGEXP_REPLACE(code, '^0+', '')
WHERE code REGEXP '^0+';

The WHERE clause avoids touching rows that already do not start with zero.

Turning It Into a Reusable Function

If the same transformation appears in many queries, a stored function can keep the SQL readable.

1DELIMITER //
2
3CREATE FUNCTION trim_leading_zeros(value_text VARCHAR(255))
4RETURNS VARCHAR(255)
5DETERMINISTIC
6BEGIN
7    RETURN REGEXP_REPLACE(value_text, '^0+', '');
8END //
9
10DELIMITER ;

Usage:

SELECT trim_leading_zeros('000X45') AS result;

That returns X45.

If your business rule says that an all-zero string such as 0000 should become 0 rather than the empty string, add a wrapper.

1DELIMITER //
2
3CREATE FUNCTION trim_leading_zeros_keep_one(value_text VARCHAR(255))
4RETURNS VARCHAR(255)
5DETERMINISTIC
6BEGIN
7    DECLARE cleaned VARCHAR(255);
8    SET cleaned = REGEXP_REPLACE(value_text, '^0+', '');
9    RETURN CASE WHEN cleaned = '' THEN '0' ELSE cleaned END;
10END //
11
12DELIMITER ;

Older MySQL Versions

If REGEXP_REPLACE is unavailable, you can still find the first non-zero character and slice from there. One approach is to use REGEXP_INSTR where available, or a more manual expression if necessary.

For purely numeric strings, developers sometimes cast to UNSIGNED, but that is not appropriate for general alphanumeric text because values like 000ABC123 do not cast meaningfully.

That distinction matters. This is a string-manipulation problem, not a numeric-conversion problem.

Data Semantics Matter

Before trimming, confirm that leading zeros are actually formatting noise. In many systems they are significant. Product codes, ZIP codes, bank identifiers, and fixed-width protocol fields often require those zeros to remain.

If the column mixes true identifiers with display values, consider creating a normalized companion column rather than overwriting the stored original. That gives you search-friendly data without losing the canonical representation.

Common Pitfalls

A common mistake is using TRIM(LEADING '0' FROM col) without checking behavior across versions and business rules. It can work for simple cases, but teams often forget to define what should happen with values such as 0000, 0A01, or empty strings.

Another mistake is casting to a number to remove zeros. That destroys non-numeric suffixes and can silently change the meaning of the data.

Developers also sometimes remove all zeros, not just leading zeros, by using an unanchored replacement. Always anchor the pattern to the beginning when the rule is “leading only.”

Finally, be careful with updates on identifier columns. If external systems still expect the padded form, trimming in place can break joins, lookups, or audit trails.

Summary

• For MySQL 8, use REGEXP_REPLACE(col, '^0+', '').
• The pattern removes only the zeros at the start of the string.
• Do not cast alphanumeric data to numeric types just to strip zeros.
• Decide explicitly how to handle all-zero strings such as 0000.
• Verify that leading zeros are not semantically important before updating stored values.