August 11, 2009 at 8:52 am
I have a dataflow task which pulls data from SQL table and dumps to csv file.
I am having problem stipping off leading zero in csv file for the zipcode field. I tried the following syntax RIGHT("0" + (DT_STR,5,1252)ZipCode,5) in Derived column but its not working either.
Any problem would be greatly appreciated.
August 11, 2009 at 9:38 am
If you really appreciate problems, I have some really juicy ones for you :laugh:
Can you provide sample data - source and required output - please? I'm English & not really familiar with zip codes ... Is there a reason for storing the leading zeroes in the SQL Server table, by the way?
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
August 11, 2009 at 9:47 am
For e.g, this is the example of zip code 07302. When going to csv, it becomes 7302.
Zip code is standard 5 digit. Leading zero must present to make the zipcode valid.
August 11, 2009 at 9:51 am
OK, I think an easy way to do this is to put in a data conversion transformation and convert from your current string field to an integer - this will strip out the leading zeros without you needing to do any extra work at all.
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
August 11, 2009 at 9:55 am
But I need the leading zero.
07302 becomes 7302 in csv file But I need 07302 in csv file. Am I making sense here?
August 11, 2009 at 10:00 am
Ah - you said you were having a problem stripping off leading zeros and that confused me, but now I understand.
So you need something like (completely untested)
LEFT('00000', 5 - LEN(Zip)) + Zip
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
August 11, 2009 at 10:09 am
In the derived column? I am getting syntax error.
August 11, 2009 at 10:34 am
Do I have to do everything? I said it was untested and expected that you could work it out ...
Turns out there is no LEFT function available, so you'll have to use SUBSTRING instead.
SUBSTRING( "00000", 1, 5-LEN(zip)) + Zip
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply