Conversion code

[dspxlearn]

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...

[chulett]

Or perhaps... StringToDecimal

[kumar_s]

Or perhaps... StringToDecimal

But i dont think StringToDecimal can remove single or doube qoutes

regards
kumar

[dspxlearn]

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...

[ray.wurlod]

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.

[dspxlearn]

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

[BIuser]

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

Hey, I think you and ray are talking about the same thing.

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?

[ray.wurlod]

Code: Select all

If Left(InLink.TheString,1) = '"' And Right(InLink.TheString,1) = '"' Then InLink.TheString[2,Len(InLink.TheString)-2] Else InLink.TheString

You may choose to strip leading and trailing white space first, perhaps in a stage variable, if there is white space outside the quote characters.

[dspxlearn]

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!!

[BIuser]

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!!

Oops, didn't look at the date or the fact that the issue is resolved.
Oh, well. Answers are answered... ...again.