Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: oracle function - get numbers from text string

Re: oracle function - get numbers from text string

From: Benjamin.Altman <benjamin.altman_at_noaa.gov>
Date: Tue, 10 Jul 2001 09:28:53 -0400
Message-ID: <3B4B0315.A1BCA2B@noaa.gov>

This seemed to work:

    DECLARE

            A VARCHAR2(20) := '$111 1234567.890abc';
            B VARCHAR2(20);
            C VARCHAR2(20);
    BEGIN
            B := TRANSLATE(A, '1234567890.', '.');
            C := TRANSLATE(A, B, ' ');
            DBMS_OUTPUT.PUT_LINE(C);

    END;
    /

"Benjamin.Altman" wrote:

> You could do a loop over the string removing numbers or try something using
> TRANSLATE function. Maybe this would work:
> chrs := TRANSLATE(mynum, '1234567890.');
> nums := TRANSLATE(mynum, chrs);
>
> where nums would contain the number part.
>
> Andy Ho wrote:
>
> > Hi,
> >
> > I was wondering if there is a function that can return numbers from string.
> > For example:
> >
> > '$ 4.50' - returns '4.50'
> > 'abc 1.0 def' - returns '1.0'
> >
> > There are no patterns for the string. Onle one thing for sure, there is only
> > one number or no number in the string (if no number, return NULL).
> >
> > Thanks, Andy
> >
> > ------------------------------------------------------------
> > Get your FREE web-based e-mail and newsgroup access at:
> > http://MailAndNews.com
> >
> > Create a new mailbox, or access your existing IMAP4 or
> > POP3 mailbox from anywhere with just a web browser.
> > ------------------------------------------------------------
Received on Tue Jul 10 2001 - 08:28:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US