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);
}
-
There's a built-in function in C# that will do this without looping:
string col1 = "0001234";col1.TrimStart('0');
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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 ssisTo replace leading zeros but not others? Please demonstrate how.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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 charactersNice trick.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
sqlbi.vvamsi (5/22/2013)
REPLACE(LTRIM(REPLACE(field,"0"," "))," ","0") is the expression, this won't work if field value has space charactersNice! 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