Using INSTR

[tokerago13]

I would like to get the number from this string. There is the possibility of the number growing to more than 5 digits. How do I go about this?

select 'Standard Purchase Order 12590 has been approved' from dual

Thanks in advance.

 

[DBAwhosaysNIE]

SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')
from dual
/

 

[markrem]

Tokerago13,

The above example is a generic one from the Oracle Document

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions150a.htm#79574.

You would probably want to customize it like

SELECT REPLACE(TRANSLATE(your_column,
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789XXXXXXXXXXXXXXXXXXXXXXXXXX'),'X',null)
from your_table;

This example first replaces all letters with an X, which can easily be removed using the REPLACE command. If you have other characters that need to be replaced, such as a space you can nest the REPLACE functions like

SELECT REPLACE(REPLACE(TRANSLATE(your_column,
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789XXXXXXXXXXXXXXXXXXXXXXXXXX'),'X',null), ' ')
from your_table;


9i OCP

 

[carp]

I'm not sure replacing one call to TRANSLATE with one call to TRANSLATE and one or two calls to REPLACE is a real pickup. A specific (non-generic) solution to the stated problem might look like:

SELECT TRANSLATE('Standard Purchase Order  12590  has been approved',
'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ',
'1234567890') 
FROM dual;

Note that the blank space is also included in the TRANSLATE string. Also, if you add other punctuation marks to your string, you will want to add those to the TRANSLATE string.

If you are going to be doing a lot of this sort of thing, you might want to consider a stored function (or else defining the function within your procedure):

CREATE OR REPLACE FUNCTION number_in_string(p_string IN VARCHAR2) RETURN NUMBER AS
   l_char_string VARCHAR2(100) := '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz .?!/"*$';
   l_num_string  VARCHAR2(10) := '1234567890';
BEGIN
   RETURN to_number(TRANSLATE(p_string,l_char_string, l_num_string));
END;

Then your task boils down to:

SELECT number_in_string('Standard Purchase Order  12590  has been approved') the_number FROM dual;

THE_NUMBER
----------
     12590

One last caveat: If your character string changes to include other numbers (e.g., 'Standard Purchase Order 12590 has been approved on 6/15/04.'), this solution will not work (since you've now added other numbers!). In this case, you'll be back to looking at the INSTR and SUBSTR functions.

Elbert, CO
1231 MDT

 

[tokerago13]

Thanks everyone for your contribution. It's now been sorted and I'm using the translate function