Removing leading zeros doesn't mean removing every zero in the value. SQL Server 2022 lets LTRIM and RTRIM accept a custom character set. You can trim only the intended boundary characters and leave the rest of the value alone.
Check the Version and the TRIM Compatibility Rule
SQL Server 2022 added the optional characters argument for these two functions. The LEADING, TRAILING, and BOTH keywords in TRIM need database compatibility level 160. In my test on SQL Server 2025, both functions accepted a character set even at levels 100 and 150. TRIM with LEADING failed with a syntax error at 150 and worked at 160.
Check both engine and database before testing. An older engine rejects the extra argument outright, and an older compatibility level rejects the TRIM keywords. The version number alone doesn't answer both questions.
I read the current level before changing it for one string expression. A compatibility change affects more than trimming, including optimizer behavior. Use an approved test database or deploy under a reviewed compatibility plan.
The query below reads the setting without changing it. If it is below 160 and you need the TRIM keywords, raise it through your ordinary database change process first. My test database reported 170. Don't conceal that dependency inside an unexplained failure.
SELECT name,compatibility_level
FROM sys.databases WHERE database_id = DB_ID();Strip Zeros Only From the Leading Edge
LTRIM(value,'0') removes zero characters from the beginning until it meets a character outside the set. It preserves zeros later in the value. This is useful for a display normalization when the identifier's business rule permits it.
Don't convert the string into a number only to remove its prefix. Numeric conversion needs its own business rule. Identifiers can contain significant text.
The sample includes an all-zero string and a zero inside the value. An all-zero input becomes an empty string under this trimming rule. Decide whether the consumer expects empty text or a single zero.
That normalization belongs in a separate CASE expression. I keep that rule explicit. Otherwise, a convenient trim changes zero into a representation the next application didn't expect.
SELECT v.InputValue,LTRIM(v.InputValue,'0') AS TrimmedValue
FROM (VALUES ('000123'),('000102'),('0000'),('A001')) AS v(InputValue);
SELECT CASE WHEN LTRIM('0000','0') = '' THEN '0'
ELSE LTRIM('0000','0') END AS NormalizedZero;In my run, 000123 became 123 and 000102 kept its inner zero as 102. The all-zero input became an empty string, A001 didn’t change, and the CASE turned the empty result into 0.
LTRIM and RTRIM Treat Characters as a Set
The optional argument names characters to remove, not a literal prefix or suffix string. RTRIM(value,'.,') removes any sequence of dots and commas at the trailing edge. Their order in the argument doesn't prescribe the suffix order.
The function stops at the first character outside that set. This distinction matters when someone expects it to remove exactly one specific ending rather than every matching boundary character.
A value ending in alternating punctuation can lose the whole matching run. Embedded punctuation remains. Choose the set narrowly and test legitimate values sharing those boundary characters.
A period can be meaningful in some identifiers or abbreviations. Trimming is a rule about accepted text, not universal cleanup. The database can't determine whether punctuation was accidental just because removing it makes the grid look tidier.
SELECT v.InputValue,RTRIM(v.InputValue,'.,') AS CleanEnding
FROM (VALUES ('sample.,.,'),('sample.part,'),('sample!,')) AS v(InputValue);The first value lost its whole run of dots and commas. The second kept its inner period, and the third stopped at the exclamation mark.
Use TRIM for One or Both Boundaries
TRIM can apply a character set to both ends or to a selected direction in the SQL Server 2022 syntax. BOTH makes the intent visible even when both-end behavior is the default. LEADING and TRAILING identify a single side.
They don't remove matching characters inside the string. The following examples keep the same input so the boundary difference is easy to inspect.
Mixing spaces with punctuation is valid when that set matches the business requirement. Order in the set still doesn't matter. Use Unicode input and a compatible character expression when needed.
The character-set argument has supported type restrictions and isn't a place for an arbitrarily large value. A short, deliberate set is easier to review than a broad cleanup list inherited from another dataset.
SELECT TRIM(BOTH '., ' FROM ' .,sample., ') AS BothEdges,
TRIM(LEADING '., ' FROM ' .,sample., ') AS LeadingEdge,
TRIM(TRAILING '., ' FROM ' .,sample., ') AS TrailingEdge;BOTH returned sample. LEADING kept the trailing dot, comma, and space, and TRAILING kept the leading ones.
Compare LTRIM and RTRIM With Old Replacement Tricks
REPLACE removes matching text throughout the value. Replacing zero with an empty string changes 000102 into 12, losing a meaningful internal zero. That is a different operation from leading trim.
Don't substitute a global replacement merely because the result looks right for 000123. Test an input with the target character in the middle before choosing a cleanup expression.
PATINDEX and SUBSTRING can implement a leading-zero rule on older engines. The all-zero input needs special handling because there is no first nonzero character to locate. The example below uses a sentinel to find that boundary safely for the declared input.
It is more cumbersome than the newer function but keeps the operation explicit. Preserve the same all-zero policy when migrating old code to the new syntax.
DECLARE @Value varchar(30) = '000102';
SELECT REPLACE(@Value,'0','') AS GlobalReplacement,
SUBSTRING(@Value,PATINDEX('%[^0]%',@Value + 'x'),LEN(@Value)) AS OldLeadingTrim,
LTRIM(@Value,'0') AS NewLeadingTrim;REPLACE returned 12, while both leading-trim versions returned 102.
Define NULL, Empty, and Collation Behavior
A NULL input remains NULL. An empty input remains empty. An input made entirely of trim characters becomes empty rather than NULL.
Keep those distinctions if the schema or API uses them differently. NULLIF can deliberately convert an empty result into NULL after trimming, but that is another business rule. A function's boundary behavior doesn't decide the missing-value policy for your application.
Which characters are valid at the edges of this field? Test them under the column's actual collation and data type. Case and character comparisons can follow collation rules, so don't assume a text set is independent of that context. Under a case-insensitive collation, LTRIM('aAb','a') returned b in my test, while a case-sensitive collation returned Ab.
Keep original values available during a bulk cleanup rehearsal. A successful UPDATE can remove meaningful data as efficiently as unwanted padding when the accepted set was chosen too broadly.
Keep LTRIM and RTRIM Cleanup Narrow and Reviewable
Preview LTRIM and RTRIM expressions before updating stored values. Include all-zero input, punctuation inside the string, NULL, empty text, and mixed edge characters in the fixture. Compare the old and new rules by stable row identity.
Keep the required compatibility level with the deployment plan. A shorter function call is valuable only when it preserves the field's approved normalization behavior across every relevant case.
Use LTRIM and RTRIM for targeted boundary cleanup and TRIM when the direction needs to be explicit. Treat the character argument as a set, and preserve internal text. A string doesn't become more correct just because it becomes shorter. The useful cleanup removes what the contract excludes while keeping the characters the business still needs to recognize the value.
Related reading on this blog: SQL Server: Performance Comparison of Function Trim and LTRIM(RTRIM) and Performance Observation of TRIM Function.
Trimming is not global replacement, it is a deliberate rule at the string's boundaries.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
