Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: oracle function - get numbers from text string
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);
"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