Paul Gu | Blog

sharing is caring

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?

Next Post

Previous Post

Leave a Reply

© 2024 Paul Gu | Blog

Theme by Anders Norén