oracle function - get numbers from text string

oracle function - get numbers from text string

Post by Andy H » Wed, 11 Jul 2001 04:29:20



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.
------------------------------------------------------------

 
 
 

oracle function - get numbers from text string

Post by Dusan Marjano » Wed, 11 Jul 2001 04:44:02


Hello!!!
Try to make PLSQL function returning number. Argument for this function can
be string with number. This function could be called even from SQL queries.
best regards
DUSAN MARJANOV


Quote:> 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.
> ------------------------------------------------------------


 
 
 

oracle function - get numbers from text string

Post by Benjamin.Altma » Wed, 11 Jul 2001 05:33:45


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.


> 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.
> ------------------------------------------------------------

 
 
 

oracle function - get numbers from text string

Post by Benjamin.Altma » Wed, 11 Jul 2001 22:28:53


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;
    /

> 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.


> > 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.
> > ------------------------------------------------------------

 
 
 

1. Function to do natural sort of string number?

Is there a function floating around that will allow natural sorting of
numbers which are strings? What I am referring is that in the series 1, 2,
3, 10 it appears in this order instead of 1, 10, 2, 3. I thought to write a
function to pad leading spaces, but then wasn't sure that was the best
choice.

Thanks for the help.  -cf

2. getting connection but no data

3. Function to return UNIQUE number from a string.

4. 3 WAYS TO INCREASE SALES . . .

5. Sorting text string as numbers

6. Integrating with Acrobat PDF forms

7. Extracting text strings and numbers

8. ADO problem

9. Robust number to text string

10. String functions for text fields

11. function to convert currency to text string

12. Getting a total hit number and a LIMIT number at the same time

13. Connecting to an Oracle 8i Database remotely....getting invalid connection string