You can use Translate(SerialNumber)
SELECT MAX(TRANSLATE(SerialNumber,
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-', '0123456789')
"MaxSerialNumber"
FROM DUAL;
"Randy Harris" <randy.harris_at_nospam.net> wrote in message
news:xSlma.11$qa5.27539_at_newssvr28.news.prodigy.com...
> 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
>
>
>
Received on Sun Apr 13 2003 - 18:35:35 CDT
