30

Dec

2010

Remove Leading Zeros from SQL Table

You might have a situation that you need to remove all leading zeros from one column in table, from now on, we call this column as “Amount”.

There is simple way if this “Amount” is integer:

SELECT
CAST(Amount AS INT) as Amount
FROM TableName

However, in many cases, this “Amount” contains decimals, here is the better solution:

(Assuming we are going to fix the problem column “Amount”)

UPDATE [dbo].[Order]
SET [Amount] = REPLACE(LTRIM(REPLACE(TRIM([Amount]), ‘0’, ‘ ‘)), ‘ ‘, ‘0’)

— Basically it performs four steps:
–   1. Trim all spaces if any to prevent trailing zeros – TRIM([Amount])
–   2. Replace each 0 with a space – REPLACE(<Step #1 Result>, ‘0’, ‘ ‘)
–   3. Use the LTRIM string function to trim leading spaces – LTRIM(<Step #2 Result>)
–   4. Lastly, replace all spaces back to 0 – REPLACE(<Step #3 Result>, ‘ ‘, ‘0’)

This solution will only work if there are no spaces within the string.

All done! Comments?

One Response

  1. Articulos…

    Hi, I cant’t access the Rss feed from your blog. Can you check that? Thanks…