SSIS Package to remove leading 0's

  • Hey Everyone, 🙂

    I am creating an SSIS package and having some issues removing leading 0's from a field. I cannot change the data type to int since the values can contain letters and numbers. Does anyone know how to remove leading 0's for vchar data without removing all the 0's from the data? Any help would be greatly appreciated.

  • You didn't mention where in your pipeline you were trying to accomplish this. If you are in a Script Task, you could do something simple like:

    string checkString = "0z700";

    while (checkString.StartsWith("0")) {

    checkString = checkString.Remove(0, 1);

    }



    Rick Krueger

    Follow @dataogre

  • There's a built-in function in C# that will do this without looping:

    string col1 = "0001234";

    col1.TrimStart('0');

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • does the number of leading zero change?

    My gut feel would be to find the position of the first non zero character using regex and then trim from there to the end of the string.

    Can I as why you need to remove the leading zeros from an AlphaNumeric string. If you actually have a datasource that is preserving them, then it must be for a reason.

    Obiron

  • you could use replace function in ssis

  • sqlbi.vvamsi (5/21/2013)


    you could use replace function in ssis

    To replace leading zeros but not others? Please demonstrate how.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • REPLACE(LTRIM(REPLACE(field,"0"," "))," ","0") is the expression, this won't work if field value has space characters

  • sqlbi.vvamsi (5/22/2013)


    REPLACE(LTRIM(REPLACE(field,"0"," "))," ","0") is the expression, this won't work if field value has space characters

    Nice trick.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • sqlbi.vvamsi (5/22/2013)


    REPLACE(LTRIM(REPLACE(field,"0"," "))," ","0") is the expression, this won't work if field value has space characters

    Nice! Thanks for posting. Was just starting to tackle a situation with lead zeros this morning. You saved me some time and I doubt if my solution would have been as elegant. 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

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