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.