Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: converting car in num
"Daniel A. Morgan" <Daniel.Morgan_at_attws.com> wrote in message
news:3B3266A4.FCE13772_at_attws.com...
> Philippe Makowski wrote:
>
> > haw can I convert a field (varchar5) in num ?
> > my field contain num and char for example I have the value 456 B or 456/
in
> > my field and I want to convert it in 456
>
> If your examples are as simple as show there is one solution. But if you
have
> examples like this "123 456 /" or "123 / 2" the solution is different. How
> representative are your examples? And are there no periods that need to be
> interpreted as decimals?
>
> In the end you will need to create a loop with a nested FOR loop. Then
step
> through each record, and within each record step through each character,
> matching for a numerical value, concatenating them as strings, and finally
> casting them as a number.
>
> Daniel A. Morgan
>
Character function TRANSLATE will eliminate the "garbage". Then TO_NUMBER .
See SQL Reference.
No PL/SQL needed.
Eugene Khatayevich Received on Fri Jun 22 2001 - 01:12:26 CDT