April 17, 2009 at 8:02 am
Hi, I have a situation here that I need to find the best way to handle:
We have some files that are extracted from Cobol (Flat Files), and loaded into SQL Server using SSIS.
The amounts in those files are specified that way:
So the value -> 0034255} means : -003425.5} since the } is in the negative part.
0034255F can be translated to 003425.56 since F is the positive 6....
Ok, I hate to say that, but that is a pain to translate to a real number, and all of that to save 1 character.. anywayz, does anyone know how to change those efficiently?
Thanks, I've pasted the corresponding table so you can understand better.
VALUE Positive Negative
0 { }
1 A J
2 B K
3 C L
4 D M
5 E N
6 F O
7 G P
8 H Q
9 I R
Edited for readability
Cheers,
J-F
April 21, 2009 at 7:25 am
When I had to deal with a similar situation, I treated the numbers with characters as strings, and passed them through a script component that isolated the special character and used a case statement to replace it with the numeric equivalent. In my case, all of them were positive (legacy account numbers) but a similar approach might work in your situation.
April 21, 2009 at 7:43 am
Thanks Dave, I'll look into the script component to do a Big select case in Vb.net, that should do it! Thanks again!
Cheers,
J-F
April 21, 2009 at 5:48 pm
I think I would handle this slightly differently.
I would split the input columns so that the numeric data is in one column and the 'special' character is in another.
I would add (in principle) three derived columns:
1) Sign (1 for positive, -1 for negative)
2) Value (0-9)
3) Final Value = Sign * (orig numeric data + Value)
To work with the special character, I would convert it to its ASCII numeric value and test whether that falls within certain ranges, rather than testing every single letter ...
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply