|
|
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:
-
Replace each 0 with a space – REPLACE([CustomerKey],
'0', ' ')
-
Use the LTRIM string function to trim leading spaces –
LTRIM(<Step #1 Result>)
-
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').
|