May 10, 2013 at 8:34 am
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.
May 10, 2013 at 1:15 pm
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);
}
May 12, 2013 at 9:39 am
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.
May 13, 2013 at 7:17 am
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
May 21, 2013 at 8:42 pm
you could use replace function in ssis
May 22, 2013 at 1:13 am
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.
May 22, 2013 at 8:02 am
REPLACE(LTRIM(REPLACE(field,"0"," "))," ","0") is the expression, this won't work if field value has space characters
May 22, 2013 at 9:09 am
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.
May 23, 2013 at 9:17 am
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