Post reply

Remove Leading Zeros

  • January 24, 2014 at 12:49 pm

    #281633

    Hello Everyone

    I am working with some odd data and ran across some of the data as having Leading Zeros. I have been trying to write a query to remove the leading zeros and everything after the blank space. From what I can tell, there are always two leading zeros. Not to say that are not more, I just have not found anything other than two leading zeros.

    Original Data:

    005863913 00002

    I am trying to code the query to make the data look like this:

    5863913

    The code that I have tried is not at all working well. I tried using substring and charindex, but I am missing something. Today feels more like a monday that a friday to me.

    Thank You in advance for all your time, suggestions and assistance.

  • January 24, 2014 at 12:55 pm

    #1683246

    Hi Andrew,

    Here are two options you might consider.

    WITH SampleData AS(

    SELECT '005863913 00002' String)

    SELECT CAST( LEFT( String, CHARINDEX( ' ', String)) AS Int) AS AsInteger,

    SUBSTRING( LEFT( String, CHARINDEX( ' ', String)), PATINDEX( '%[^0]%', String), 8000) AS ASString

    FROM SampleData

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 24, 2014 at 1:12 pm

    #1683248

    Thanks Luis

    That worked perfectly.

    I hope that you have a very nice weekend

    Andrew SQLDBA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply