Conversion code
Moderators: chulett, rschirm, roy
Conversion code
hi,
I have a requirement to pass the source data which may contain some text characters (like quotes), to the target column removing them.I mean i need only the numeric characters in my target. Source is of data type Decimal and target datatype is varchar.
For example:-
Eg1:-
ToNumber("123.45")
Should return=123.45
Eg2:-
ToNumber({file.REFERENCE})
Should Return 200.00 where "200" is the text string in the file.REFERENCE} field.
I believe, in server jobs we can specify by....
Conversion Expression Internal Value
X = Iconv("John Smith 1-234", "MCN") X = "1234"
But as i am in parallel jobs i cannot use those Oconv and Iconv functions..So, how can i solve this....
Can anyone share your ideas...
I have a requirement to pass the source data which may contain some text characters (like quotes), to the target column removing them.I mean i need only the numeric characters in my target. Source is of data type Decimal and target datatype is varchar.
For example:-
Eg1:-
ToNumber("123.45")
Should return=123.45
Eg2:-
ToNumber({file.REFERENCE})
Should Return 200.00 where "200" is the text string in the file.REFERENCE} field.
I believe, in server jobs we can specify by....
Conversion Expression Internal Value
X = Iconv("John Smith 1-234", "MCN") X = "1234"
But as i am in parallel jobs i cannot use those Oconv and Iconv functions..So, how can i solve this....
Can anyone share your ideas...
Thanks and Regards!!
dspxlearn
dspxlearn
But i dont think StringToDecimal can remove single or doube qouteschulett wrote:Or perhaps... StringToDecimal
regards
kumar
Hi kumar ,
Your approach was good...As you said the convert function can be useful only when you are sure which string character is coming along with the numerical value from the source....But, my requirement is take only the numerical value from the source by removing string characters if any..And i dont want that to be converted into something.......it means i need to kick off those characters from the source...
Your approach was good...As you said the convert function can be useful only when you are sure which string character is coming along with the numerical value from the source....But, my requirement is take only the numerical value from the source by removing string characters if any..And i dont want that to be converted into something.......it means i need to kick off those characters from the source...
Thanks and Regards!!
dspxlearn
dspxlearn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The source is a string, so you can use Trim() or Convert() to remove characters. Convert them to zero-length strings ("") and they disappear from consideration.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi ray,
i understood what you have written..But i believe these convert,Trim functions can be used only when we are very much sure about which characters are coming along with the source....But here in my requirement i am not sure about them...they can be either one of these like... " , - , _ ,
Eg:-
Source:- "200" Output:- 200
Source:- 123_45 Output:- 1234
i understood what you have written..But i believe these convert,Trim functions can be used only when we are very much sure about which characters are coming along with the source....But here in my requirement i am not sure about them...they can be either one of these like... " , - , _ ,
Eg:-
Source:- "200" Output:- 200
Source:- 123_45 Output:- 1234
Thanks and Regards!!
dspxlearn
dspxlearn
Hey, I think you and ray are talking about the same thing.dspxlearn wrote:Hi ray,
i understood what you have written..But i believe these convert,Trim functions can be used only when we are very much sure about which characters are coming along with the source....But here in my requirement i am not sure about them...they can be either one of these like... " , - , _ ,
Eg:-
Source:- "200" Output:- 200
Source:- 123_45 Output:- 1234
Try this; In your transformer, create a stage variable of the same type as your source column. In your derivation of your newly created stage variable put something like this:
Convert('"', '', Convert('-', '', Convert('_','', YOUR_COLUMN_NAME_HERE)))
Then use the stage variable as your output for that column of the same name as your input column (i.e. the one you used for "YOUR_COLUMN_NAME_HERE").
If the convert function finds the specified char in the input, it will replace it with nothing (also known as removing it ), if it finds nothing, it does nothing and just passes the data on.
Is this at all what you need to do?
-------------------------
https://www.ssa.co.za
https://www.ssa.co.za
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
If Left(InLink.TheString,1) = '"' And Right(InLink.TheString,1) = '"' Then InLink.TheString[2,Len(InLink.TheString)-2] Else InLink.TheString
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Oops, didn't look at the date or the fact that the issue is resolved.dspxlearn wrote:Posted: Wed Sep 28, 2005 11:49 pm
I have posted this couple of years back!!
This was already discussed.
Biuser,
Answers are answered now!!
Oh, well. Answers are answered... ...again.
-------------------------
https://www.ssa.co.za
https://www.ssa.co.za