String to number (with decimal)

[jaburke]

Hi,
In a universe object, I am trying to convert a string to a number if it's all numeric. This is working fine - almost all of the time - using this formula:

DECODE( TRANSLATE(@Select(Class\Field),'_0123456789',' '), NULL, 'number','char')

So I am converting to 'number' or 'char'. If it returns 'number', I convert it to a number. The problem is when the value in the string is 100.00, for example. It thinks it's a string because of the decimal. How can I modify this logic to make a string, i.e. 100.00 evaluate so that I can convert it to a number?

Thanks!!

 

[Madawc]

Is this Crystal? Crystal has NumericText, which should do the job.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[jaburke]

No, I'm building a webi report off a universe and that's where I've created my object. I finally got this to work with a small tweak to my formula above. I changed it to this:

DECODE( TRANSLATE(@Select(Class\Field),'_.0123456789',' '), NULL, 'number','char')

I added a decimal after the underscore and this works. Thanks!