Tip of the Day : SQL Server Numeric Data Types
Trim Leading Zeros Function

Trim Leading Zeros Function

Update: If you are using SQL Server 2022, proceed to the Trim Leading Zeros Using the LTRIM String Function section below. Otherwise, please continue reading on how to trim leading zeros.

One requirement that I was recently asked to do was to remove or trim the leading zeros in an alphanumeric column.  The first thing that came to mind was to convert the VARCHAR column into an integer data type column then convert it back to VARCHAR and this will take care of trimming the leading zeros.  You can either use the CAST function or the CONVERT function to convert the data type from type to another.  I will use the CAST function as shown in the following: 

DECLARE @LeadingZeros    VARCHAR(10)
SET @LeadingZeros = '0000012345'
SELECT @LeadingZeros AS [Leading0s],
       CAST(CAST(@LeadingZeros AS INT) AS VARCHAR(10))
       AS [Trimmed0s]

Leading0s   Trimmed0s
----------- ----------
0000012345	12345

The CAST(@LeadingZeros AS INT) took care of removing the leading zeros because integer values (or any numeric values) do not include any leading zeros in its value.  Since the original data type was a VARCHAR, another CAST function was used to put it back to VARCHAR data type.

However, executing this process over the actual table generated the following error:

Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value '0000123-45' to a column of data type int.

That?s where I found out that the column does not just contain numeric characters but also includes alphanumeric characters.  To workaround this error, I made use of the ISNUMERIC function so that I will only trim the leading zeros of numeric values.

UPDATE [dbo].[Customers]
SET [CustomerKey] = CAST(CAST([CustomerKey] AS INT) AS VARCHAR(10))
WHERE ISNUMERIC([CustomerKey]) = 1
GO

This would have been a good workaround but the requirement was to remove or trim the leading zeros of all records and not just those where the column was numeric.  The next solution that I thought of was to simply replace all zeros with an empty string:

UPDATE [dbo].[Customers]
SET [CustomerKey] = REPLACE([CustomerKey], '0', '')

This solution spelled trouble for me because it also replaced the 0?s that are inside the string and which are not part of the leading zeros.  So, the value '000120-412' became '12-412'.  Good thing I had a back-up and I was able to restore the table back to its original.

After playing around with the different string functions available in SQL Server, I came up with the following solution:

UPDATE [dbo].[Customers]
SET [CustomerKey] = REPLACE(LTRIM(REPLACE([CustomerKey], '0', ' ')), ' ', '0')

Basically it performs three steps:

  1. Replace each 0 with a space ? REPLACE([CustomerKey], '0', ' ')
  2. Use the LTRIM string function to trim leading spaces ? LTRIM(<Step #1 Result>)
  3. Lastly, replace all spaces back to 0 ? REPLACE(<Step #2 Result>, ' ', '0')

This solution will only work if there are no spaces within the string, which is the case for me.

User-Defined Function

Now to make it easier to remember how to trim leading zeros from an alphanumeric string, we can make those 3 steps into a function, as follows:

CREATE FUNCTION [dbo].[ufn_TrimLeadingZeros] ( @Input VARCHAR(50) )
RETURNS VARCHAR(50)
AS
BEGIN
    RETURN REPLACE(LTRIM(REPLACE(@Input, '0', ' ')), ' ', '0')
END
GO

Remembering the name of the function will be easier than to remember the 3 steps.  To use it in my original situation:

UPDATE [dbo].[Customers]
SET [CustomerKey] = [dbo].[ufn_TrimLeadingZeros] ( [CustomerKey] )

User-Defined Function Extended

The Trim Leading Zeros user-defined function can be extended so that it will not just trim leading zeros but it can also trim any character you want.  By simply passing the character you want to trim as a parameter, the user-defined function can trim any leading character you specify:

CREATE FUNCTION [dbo].[ufn_TrimLeadingCharacters] ( @Input VARCHAR(50), @LeadingCharacter CHAR(1) )
RETURNS VARCHAR(50)
AS
BEGIN
    RETURN REPLACE(LTRIM(REPLACE(@Input, ISNULL(@LeadingCharacter, '0'), ' ')), 
                   ' ', ISNULL(@LeadingCharacter, '0'))
END
GO

If you pass a NULL value for the @LeadingCharacter parameter, 0 is the character to be trimmed, as specified by ISNULL(@LeadingCharacter, '0').

Trim Leading Zeros Using the LTRIM String Function

Prior to SQL Server 2022, the `LTRIM` string function removes just space character char(32) from the start of a string. However, starting from SQL Server 2022, the `LTRIM` string function was updated to include the removal of other special characters instead of just the space character char(32) from the start of a string.

Syntax

Syntax for SQL Server prior to SQL Server 2022 (16.x):

LTRIM ( character_expression )

Syntax for SQL Server 2022 (16.x) and later, Azure SQL Managed Instance, Azure SQL Database, Azure Synapse Analytics, and Microsoft Fabric:

Important: You need your database compatibility level set to 160 to use the optional characters argument.

LTRIM ( character_expression , [ characters ] )

Arguments

character_expression

An expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type, except text, ntext, and image, that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

characters

A literal, variable, or function call of any non-LOB character type (nvarchar, varchar, nchar, or char) containing characters that should be removed. nvarchar(max) and varchar(max) types aren't allowed.

Return types

Returns a character expression with a type of string argument where the space character char(32) or other specified characters are removed from the beginning of a character_expression. Returns NULL if input string is NULL.

If you are using SQL Server 2022, you can use the LTRIM string function to trim leading zeros from the start of a string. Simply pass a value of '0' as the second argument of the LTRIM function:

SELECT [Input Type], [Input], LTRIM([Input], '0') AS [Output]
FROM (VALUES ('All Numbers with Leading Zeros', '000123456789'),
             ('All Zeros', '00000000000'),
             ('Number Separated by Dashes', '0000123-456-789'),
             ('MM/dd/yyy Date Format', '09/09/2024'),
             ('All String', 'No Zeros To Trim Here'),
             ('Leading Spaces','  0000888-8888'),
             ('Number with No Leading Zeros', '18005551234'),
             ('Alphanumeric String', '00An-Alphanumeric-String-00'))
AS [SampleStrings] ( [Input Type], [Input] );

| Input Type                     | Input                       | Output                    |
|--------------------------------|-----------------------------|---------------------------|
| All Numbers with Leading Zeros | 000123456789                | 123456789                 |
| All Zeros                      | 00000000000                 |                           |
| Number Separated by Dashes     | 0000123-456-789             | 123-456-789               |
| MM/dd/yyy Date Format          | 09/09/2024                  | 9/09/2024                 |
| All String                     | No Zeros To Trim Here       | No Zeros To Trim Here     |
| Leading Spaces                 |   0000888-8888              |   0000888-8888            |
| Number with No Leading Zeros   | 18005551234                 | 18005551234               |
| Alphanumeric String            | 00An-Alphanumeric-String-00 | An-Alphanumeric-String-00 |

Figure 1: Trim Leading Zeros Using LTRIM String Function