handling of String to Integer conversion

[dsxdev]

Hi,
I am getting alpha numeric value in a String column ex"1232123 ABZC".
Now I need to extract these two values to seperate columns.
I tried Field function to extract these two values seperatly.
But when I try to convert the first part of the string to numeric value (i used StringToDecimal funtion ) the job gives compilation error saying
"decimal_to_string( <line no> ,<character position>) "
"Possible range limitation"
and job does not compile.

Same job compiles with out an problem once you remove the function and compile succees fully and again use the function and compile. This time job compiles with out problem.

There is a funtion to convert numbers to integer. But this function takes numeric values and to convert to input to numeric I need to use StringTo Decimal and the story comes square one.

Can anybody tell why this happens and what is the solution.

[ray.wurlod]

If you're in a position to use BASIC functions, these are the ones you want.

• Oconv(TheString, "MCN") keeps only the numeric characters.
  Oconv(TheString, "MC/N") keeps only the non-numeric characters.
  Oconv(TheString, "MCA") keeps only the alphabetic characters.
  Oconv(TheString, "MC/A") keeps only the non-alphabetic characters.

Otherwise, I suspect you'll need to write a custom operator or two that process the string one character at a time to achieve the functionality.

[kura]

I am sorry if I ask you more details. From experience, when you design PX job data type and there length are so important. Can you give more details like what are the data type output fields. Because once converted into decimal using StringToDecimal, you might want to do casting using Integer function.

Thanks


[quote="dsxdev"]Hi,
I am getting alpha numeric value in a String column ex"1232123 ABZC".
Now I need to extract these two values to seperate columns.
I tried Field function to extract these two values seperatly.
But when I try to convert the first part of the string to numeric value (i used StringToDecimal funtion ) the job gives compilation error saying
"decimal_to_string( <line no> ,<character position>) "
"Possible range limitation"
and job does not compile.

Same job compiles with out an problem once you remove the function and compile succees fully and again use the function and compile. This time job compiles with out problem.

There is a funtion to convert numbers to integer. But this function takes numeric values and to convert to input to numeric I need to use StringTo Decimal and the story comes square one.

Can anybody tell why this happens and what is the solution.[/quote]

[cbrandel]

Hi,

if Your data always looks like "1232123 ABZC" then You can also try the ColumnImport Stage. There You can specify an input column that will be split into output columns. Much like reading from a sequential file. In Your case specify "whitespace" as separator, define an integer and a varchar as output columns and You should be all set.

Hope that helps,
Chris

[dsxdev]

Hi,
I am getting alpha numeric value in a String column ex"1232123 ABZC".
Now I need to extract these two values to seperate columns.
I tried Field function to extract these two values seperatly.
But when I try to convert the first part of the string to numeric value (i used StringToDecimal funtion ) the job gives compilation error saying
"decimal_to_string( <line no> ,<character position>) "
"Possible range limitation"
and job does not compile.

Same job compiles with out an problem once you remove the function and compile succees fully and again use the function and compile. This time job compiles with out problem.

There is a funtion to convert numbers to integer. But this function takes numeric values and to convert to input to numeric I need to use StringTo Decimal and the story comes square one.

Can anybody tell why this happens and what is the solution.

I am using this function in a transformer and my experience says BASIC transformer reduces performance drastically.

My input column type is varchar(26). I need the split this field into two parts one Numeric and remaining.This numeric field can vary from 1 to 26 digit long. Usually I get numbers of 12 to 16 digit long in this field.

I am extracting teh field and Type casting it to BigInt(18) then also this problem persists. As soon as I introduce this StringToDecimal function I get this problem.

Any inputs would be helpful.