Convert Text to Number or Not

[szeiss]

I have a column in an oracle table with datatype varchar2. That column can have either text or numbers. If it's text then display the text but if it's a number I need to display the number as in 55,555,555,555 or $55,555,555,555.


Thanks,
Sherry

 

[lbass]

We need to see samples of how the field currently displays--then show what you would expect to see for each.

-LB

 

[szeiss]

Is this what you need?

Oracle Column is:
ACTUALS, VARCHAR2(200 BYTE), DEFAULT NULL

Examples of data in column are:
Yes (Expect to see the word Yes)
No (Expect to see the word No)
9942.94 (Expect to see the number 9,943 or $9,943 - haven't decided about $$ signs yet)
96 (Expect to see 96)

Thanks,
Sherry

 

[lbass]

Okay, try this:

if isnumeric({table.string}) then
totext(tonumber({table.string}),0) else
{table.string}

-LB

 

[szeiss]

Thanks, I tried that except I used a 2 in place of the 0 for decimal places. It works except when I want a whole number instead of 2 decimal places. Any ideas?

Thanks,
Sherry

Examples of data in column are:
Yes (Expect to see the word Yes)
No (Expect to see the word No)
9942.94 (Expect to see the number 9,943 or $9,943 - haven't decided about $$ signs yet)
96 (Expect to see 96)
0.048 (Expect to see 0.048) ** I added one **

 

[Turkbear]

Hi,
Maybe use LB's Formula and add a test for decimal existance:

if isnumeric({table.string}) then
 If Truncate(tonumber({table.string})=
    tonumber({table.string})
   Then
      totext(tonumber({table.string}),0)
    else
    totext(tonumber({table.string}),2)
Else
{table.string}

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[szeiss]

Thank you, thank you. Worked perfectly. I appreciate the assistance.

Sherry

 

[lbass]

Hmmm. I think Turkbear's formula just leaves the numbers in the format they are already in, except that I think the .048 would be rounded to .05. It looks to me like you were saying you wanted no decimals if the integer value was > 0, i.e., my interpretation of your last post was that you would expect to see:

Yes
No
9943 (rounded, no decimal)
96 (no decimal)
.048 (decimals as shown)

I think the formula should then be:

stringvar x := {table.string};
if isnumeric(x) then
(
if truncate(tonumber(x)) > 0 then
totext(tonumber(x),0) else
x
) else
x

-LB

 

[szeiss]

Thanks I'll keep yours for future reference, but Turkbear's gave me what I needed. In your formula instead of getting 0.48 I got .48

Thanks,
Sherry

 

[Turkbear]

Hi,
I did miss the .048, which would get rounded, so leaving the < 0 numbers in their original format while truncating those above 0 with decimals would seem to meet the criteria:

if isnumeric({table.string}) then
 If Truncate(tonumber({table.string})=
    tonumber({table.string})[COLOR=green]// Is an integer so no decimals[/color]
   Then
      totext(tonumber({table.string}),0)
    else  [COLOR=green] // Is Not an integer[/color]
     If Truncate(tonumber({table.string}) < 0 
      then
       totext(tonumber({table.string}))[COLOR=green]// leave the number - show all decimals[/color]
      else 
       totext(tonumber({table.string}),0)[COLOR=green]//> 0 round/truncate - if want decimals replace 0 with # wanted[/color]
 Else
{table.string}

[profile]
[COLOR=blue]
To Paraphrase:"The Help you get is proportional to the Help you give.."
[/color]