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

sql-delete-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.

OUTPUT

sql-delete-zeroes


Did you like this post?
kick it on DotNetKicks.com
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 www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

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

    SERIOUSLY !!

  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-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions