---

Oracle / PLSQL: Extract arithmetic operators from a string

Question: I'd like to know how to handle this issue. I have a stored procedure that has an input variable:

v_in varchar2(200);

The value will be something like this:

1+2*30+40

I need to extract only the arithmetic operators (+, *, and +) so that I can use them in a query. How can I do this?

Answer: To extract the arithmetic operators from your varchar2 variable, you will need to use both the translate and REPLACE functions.

For example,

TRANSLATE('1+2*30+40','01234',' ')
Result: '+* +'

REPLACE('+* +', ' ')
Result: '+*+'

So you could combine these functions into:

REPLACE(TRANSLATE('1+2*30+40','01234',' '), ' ')
Result: '+*+'

Learn more about the TRANSLATE function.

Learn more about the REPLACE function.