Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Package to remove leading 0's Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 8:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 1:50 PM
Points: 8, Visits: 33
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.
Post #1451626
Posted Friday, May 10, 2013 1:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:24 PM
Points: 262, Visits: 681
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
Post #1451723
Posted Sunday, May 12, 2013 9:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 5,174, Visits: 12,028
There's a built-in function in C# that will do this without looping:

string col1 = "0001234";
col1.TrimStart('0');




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1451908
Posted Monday, May 13, 2013 7:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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
Post #1452091
Posted Tuesday, May 21, 2013 8:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 54, Visits: 380
you could use replace function in ssis
Post #1455282
Posted Wednesday, May 22, 2013 1:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 5,174, Visits: 12,028
sqlbi.vvamsi (5/21/2013)
you could use replace function in ssis


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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1455330
Posted Wednesday, May 22, 2013 8:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 54, Visits: 380
REPLACE(LTRIM(REPLACE(field,"0"," "))," ","0") is the expression, this won't work if field value has space characters
Post #1455523
Posted Wednesday, May 22, 2013 9:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 5,174, Visits: 12,028
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1455568
Posted Thursday, May 23, 2013 9:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
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.
Post #1456049
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse