Trim Leading Zeros Function

Trim Leading Zeros Function

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()
3. Lastly, replace all spaces back to 0 – REPLACE(, ' ', '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').