Re: Simply question about TO_NUMBER function

MariMax wrote:
>
> "Tom Dyess" <tdyess_at_dysr.com> ha scritto nel messaggio
> news:u1fMd.23624$t67.10929_at_bignews5.bellsouth.net...
> >
> > SELECT * FROM Table1 WHERE Field1 = TO_CHAR(456)
> >
>
> It don't work because if i have values '456' and '0456' and '00456', i want
> the query return three records not only the first.
> I can't define the field NUMERIC because it may be alphanumeric.

create a function based index on get_val(col)

or to get all the valid rows you could try something like

select /*+ ORDERED_PREDICATES */
from ...
where replace(translate('+-.0123456789','9999999999999'),'9',null) is null
and get_val(col) is not null

hth
connor

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------