Re: Need SQL number conversion

"Randy Harris" <randy.harris_at_nospam.net> writes:

> I need to find the max numeric value in a field that contains some non
> numeric data, such as
>
> 31528
> 63595
> X2187
> 77141
> 35-28
>
> records with non numeric data can be ignored.
>
> I've tried:
>
> SELECT MAX(TO_NUMBER(SerialNumber)) FROM XYZ.COUPON;
>
> but the TO_Number stumbles on the non numeric data.
>
> I would grateful for any help.
>
> --
> Randy Harris
>
>
>

I would probably use a plsql function which either returns the number if it is a number or returns null if it is not a pure number. However, if you just want to use SQl, the following may suggest a solution

select decode(translate(value,'A.1234567890','A'),NULL, value, NULL)   from table

The translate statement will remove all numbers and the ., leaving either null if it is a number or non-null if it contains any characters not a number (ignoring things like scientific notation etc). The decode statement will return the value (number) if the result of the translate is null and returns null otherwise (i.e. was not a number). Depending on the types of numbers you need to deal with, this may or may not meet your needs.

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!