December 14, 2011

Deleting Leading Zeros in a String (SQL)

A common issue while importing data from different source into SQL Server is often numbers get prefixed with zeroes. If you want to delete these leading zeroes in a string, you can use the following methods:

Method 1 : CAST to Bigint

declare @s varchar(100)
set @s ='0000004007340007402100'
select cast(@s as bigint)

CASTing the string to Bigint will automatically delete the leading zeroes


Method 2 : Use Replace function

declare @s varchar(100)
set @s ='0000004007340007402100'
select replace(ltrim(replace(@s,'0',' ')),' ','0')

The T-SQL code shown above first replaces all zeroes to a single space. The LTRIM function deletes all leading spaces and the second replace function, replaces all spaces to zeroes, so that all leading zeroes are deleted.



Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at and a moderator at His T-sql blog is at



2 Responses to "Deleting Leading Zeros in a String (SQL)"
  1. Anonymous said...
    December 15, 2011 at 2:15 PM


  2. Anonymous said...
    December 16, 2011 at 3:20 PM

    What if the string does not contains only digits? Can this computer science breakthrough be applied to "1" removal with some little modifications?


Copyright © 2009-2016 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions