SQL Remove Leading Zeros
Remove leading zeros in SQL by treating the value as text and removing 0 only from its left side; do not convert identifier columns to numbers unless their numeric meaning is certain. The safe query also needs to decide what an all-zero value such as 000 should become and must preserve NULL values. This guide focuses on SQL Server first, then shows equivalent approaches for MySQL, PostgreSQL, SQLite, and Oracle.
What Leading Zeros Mean in SQL
A value such as 00042 can mean one of two very different things:
- A numeric value stored as text, where
00042and42mean the same thing. - A formatted identifier, such as an invoice number, ZIP code, product SKU, or account code, where the zeros may be required.
Leading zeros are formatting when a column is text, but they are already absent from a true numeric value.
Before changing stored data, check the column’s purpose and type. If account_code is a varchar identifier and other systems expect five characters, replacing 00042 with 42 may break joins, exports, or validation rules.
Also decide how to handle these inputs:
| Original value | Common normalized result | Reason |
|---|---|---|
00042 |
42 |
Removes only the prefix zeros |
0001200 |
1200 |
Keeps meaningful trailing zeros |
0 |
0 |
Keeps the numeric zero representation |
000 |
0 |
Uses one consistent zero representation |
NULL |
NULL |
Missing data is not zero |
00042 |
42 |
Only when surrounding spaces are unwanted |
How to Remove Leading Zeros in SQL Server
Choose the SQL Server method that matches both your server release and the database compatibility level.
| SQL Server situation | Use | Why |
|---|---|---|
| SQL Server 2022 (16.x) or later, compatibility level 160 | LTRIM(column, '0') |
Removes specified characters from the left side only |
| SQL Server 2022 with leading spaces to remove too | Clean spaces first, then use LTRIM(column, '0') |
Keeps zero trimming separate from whitespace handling |
| SQL Server 2019 or earlier, or compatibility below 160 | PATINDEX and SUBSTRING |
Older LTRIM cannot trim zero characters |
Considering TRIM('0' FROM column) |
Do not use it for this job | It can remove trailing zeros too |
Step 1: Check the SQL Server Version and Compatibility Level
SQL Server’s plain TRIM function is available in SQL Server 2017 (14.x) and later. However, positional TRIM options such as LEADING, and the optional character list in LTRIM, require SQL Server 2022 (16.x) or later and database compatibility level 160.
Check both the server version and the compatibility level of the database you are connected to:
SELECT @@VERSION AS sql_server_version;
SELECT compatibility_level
FROM sys.databases
WHERE name = DB_NAME();
On SQL Server 2022 or later with compatibility level 160, the relevant syntax is LTRIM ( character_expression , [ characters ] ). The older form, LTRIM ( character_expression ), removes leading spaces only; it does not remove leading 0 characters.
Step 2: Preview the Cleaned Values With a SELECT Query
Start with a read-only query. The following SQL Server 2022+ query removes unwanted outer spaces, strips leading zeros, keeps one zero for all-zero input, and preserves NULL.
WITH source AS (
SELECT
account_code,
NULLIF(LTRIM(RTRIM(account_code)), '') AS code_text
FROM dbo.ImportStage
)
SELECT
account_code AS original_code,
CASE
WHEN code_text IS NULL THEN NULL
WHEN LTRIM(code_text, '0') = '' THEN '0'
ELSE LTRIM(code_text, '0')
END AS normalized_code
FROM source;
This query deliberately uses LTRIM(code_text, '0'), rather than a general TRIM call. For example, 0001200 becomes 1200, not 12.
The NULLIF expression turns blank or space-only input into NULL. That is often useful for imported numeric codes, but remove that part if blank strings have a distinct meaning in your application.
LTRIM returns NULL when its input is NULL, so trimming does not turn missing data into an empty string or the number zero.
If you want to remove leading spaces and zeros as one character set on SQL Server 2022+, this expression also works:
LTRIM(account_code, ' 0')
It removes leading space characters and zeros, but it does not remove spaces inside a value such as 000 42. Internal spaces should usually be treated as invalid data rather than silently removed.
An alternative is positional TRIM syntax:
SELECT TRIM(LEADING '0' FROM account_code) AS normalized_code
FROM dbo.ImportStage;
Here, LEADING is the positional option that removes specified characters from the start only. SQL Server requires database compatibility level 160 for LEADING, TRAILING, and BOTH. Avoid this non-positional form for zero cleanup:
SELECT TRIM('0' FROM account_code)
FROM dbo.ImportStage;
That version trims from both ends, so it can remove meaningful trailing zeros. SQL Server’s TRIM documentation states that LEADING, TRAILING, and BOTH require compatibility level 160.
Step 3: Use a Compatible Query on Older SQL Server Versions
For SQL Server 2019, SQL Server 2017, or a database that cannot use compatibility level 160, use PATINDEX to locate the first character that is not zero.
WITH source AS (
SELECT
account_code,
NULLIF(LTRIM(RTRIM(account_code)), '') AS code_text
FROM dbo.ImportStage
)
SELECT
account_code AS original_code,
CASE
WHEN code_text IS NULL THEN NULL
WHEN code_text NOT LIKE '%[^0]%' THEN '0'
ELSE SUBSTRING(
code_text,
PATINDEX('%[^0]%', code_text),
LEN(code_text)
)
END AS normalized_code
FROM source;
PATINDEX('%[^0]%', code_text) finds the first character that is not 0. The NOT LIKE '%[^0]%' branch handles 0, 00, and 000 before PATINDEX runs.
In older SQL Server releases, LTRIM ( character_expression ) removes leading space characters only. It is useful for the LTRIM(RTRIM(...)) whitespace cleanup in this query, but it is not a leading-zero removal function. SQL Server’s LTRIM documentation documents the SQL Server 2022 character-list addition.
Step 4: Store the Normalized Value Without Destroying the Original
A separate normalized column is safer than overwriting an imported identifier. For example, after adding a suitable normalized_code column, populate it in a transaction:
Run this only on SQL Server 2022+ with compatibility level 160 because it uses the two-argument LTRIM form. For older versions, use a CASE expression with PATINDEX and SUBSTRING to normalize the value in the UPDATE.
Keeping both columns lets you retain the received value for auditing or fixed-width exports while joining and reporting on the normalized value.
Remove Leading Zeros in Other SQL Databases
Many database systems support TRIM(LEADING '0' FROM column). It removes every zero at the start, not just the first zero.
MySQL and PostgreSQL
In MySQL and PostgreSQL, this query returns 42 for 00042, preserves trailing zeros, converts all-zero values to 0, and leaves NULL as NULL.
SELECT
CASE
WHEN account_code IS NULL THEN NULL
WHEN TRIM(LEADING '0' FROM account_code) = '' THEN '0'
ELSE TRIM(LEADING '0' FROM account_code)
END AS normalized_code
FROM import_stage;
If leading and trailing spaces are unwanted as well, clean those spaces before applying the leading-zero expression.
SQLite
SQLite uses ltrim(value, characters) for this operation:
SELECT
CASE
WHEN account_code IS NULL THEN NULL
WHEN ltrim(account_code, '0') = '' THEN '0'
ELSE ltrim(account_code, '0')
END AS normalized_code
FROM import_stage;
The second argument is a set of characters SQLite should remove from the left. Use '0' when you want to remove zeroes only.
Choose Text Trimming or Numeric Conversion
Use text trimming when the column is a code or identifier. Use numeric conversion only when the values are genuinely numbers and the resulting numeric type fits every valid value.
For a numeric field stored incorrectly as text in SQL Server, conversion removes leading zeros naturally:
SELECT TRY_CONVERT(bigint, account_code) AS account_number
FROM dbo.ImportStage;
Review conversion results and distinguish invalid text from genuine missing values before using the query for cleanup. That can help identify bad import rows, but it also means you must distinguish invalid text from genuine missing values.
Do not use numeric conversion for:
- Product SKUs such as
000042 - Customer IDs where
00042and42might be separate records - ZIP codes and other fixed-width codes
- Large identifiers that may exceed integer limits
- Decimal strings where the displayed precision matters
Casting to an integer does not “truncate” a number in the usual mathematical sense. It changes the data type, which removes display formatting such as leading zeros.
Normalize once into a canonical value when repeated joins need it, rather than applying cleanup functions to both sides of every join.
For example, values such as 00125 and 125 become the same normalized key. That may be correct, but it can also reveal duplicate records that were previously hidden by formatting. A persisted normalized column also avoids wrapping join columns in functions during every query.
Validate the Result and Prevent Future Inconsistent Values
Test the logic against values that cover the edge cases before changing a production table:
00042should become42.0001200should become1200.0and000should become0if that is your chosen convention.NULLshould remainNULL.- A value containing letters, such as
000AB12, should becomeAB12only if alphanumeric codes are allowed. - A value with embedded spaces, such as
000 42, should be reviewed rather than silently repaired.
After populating a normalized column, find values that still begin with zero but are not exactly zero:
SELECT normalized_code
FROM dbo.ImportStage
WHERE normalized_code LIKE '0%'
AND normalized_code <> '0';
Check whether normalization created collisions:
SELECT
normalized_code,
COUNT(*) AS source_rows
FROM dbo.ImportStage
GROUP BY normalized_code
HAVING COUNT(*) > 1;
Some duplicates may be valid. Others may show that two supposedly different identifiers, such as 00125 and 125, should not have been normalized together.
To prevent the issue at ingestion, use a numeric column for true quantities and numeric keys. For fixed-width identifiers, keep the text type and enforce the expected format rather than stripping zeros after import.
Frequently Asked Questions
What does LTRIM do in SQL?
LTRIM removes characters from the left side of a string. In SQL Server versions before SQL Server 2022 (16.x), LTRIM ( character_expression ) removes leading spaces only. SQL Server 2022+ can use LTRIM(column, '0') to remove leading zeros when the connected database compatibility level is 160.
How do I remove the first zero from a value in MySQL?
Use TRIM(LEADING '0' FROM column_name) to remove all leading zeros, not only one. For 00042, it returns 42; for an all-zero value, wrap it in a CASE expression if you want the result to be 0 instead of an empty string.
How do I trim leading zeros in Oracle SQL?
Use TRIM(LEADING '0' FROM column_name) in Oracle. The expression keeps missing input separate from the chosen zero representation.
SELECT
CASE
WHEN account_code IS NULL THEN NULL
ELSE NVL(TRIM(LEADING '0' FROM account_code), '0')
END AS normalized_code
FROM import_stage;
How do I remove leading zeros and spaces from a SQL Server column?
On SQL Server 2022+ with compatibility level 160, first remove unwanted outer spaces with LTRIM(RTRIM(column_name)), then apply LTRIM(..., '0'). This removes outer space characters and leading zeros while preserving zeros at the end of values such as 0001200.
How do I remove leading zeros for a join in Open SQL?
Treat the identifier rule as part of the join design: decide whether leading zeros are significant. Normalize the join key before the join, preferably when staging or importing the data, or use an application-layer conversion designed for the relevant identifier format. Store and join on the normalized key when 00125 and 125 are intended to represent the same record.
How do I get rid of leading zeros in Excel?
For true numbers in Excel, use =VALUE(A1) and then paste the results as values if needed. Do not use that approach for identifiers such as ZIP codes or account codes, because Excel will convert them to numbers and permanently remove the formatting zeros.
How do you add leading zeros in SQL Server?
To pad a SQL Server string to three characters, use RIGHT(‘000’ + ISNULL(field, ”), 3). For a numeric field, cast it to VARCHAR(3) before concatenating. This approach only applies when the input is no longer than the chosen width; for a different width, change the zero string and width values together.
