Regular Expression IN SQL

Question

I have to make a regular expression where I need to replace all the words of a dynamic query in ORACLE with NULL except for those words that begin with the @ character. For example:

SQL:

SQL: SELECT @param1, column2, column3, @param2 FROM dual WHERE @code = code_table AND amount > @param4 + 50

Using REGEXP_REPLACE

DECLARE
    vl_result VARCHAR2(1000);
BEGIN
    vl_result := REGEXP_REPLACE('SELECT @param1, column2, column3, @param2 FROM dual WHERE @code = code_table AND amount > @param4 + 50', 'EXP_REG', '');
    dbms_output.put_line(vl_result);
END;

should have the following result:

@param1@param2@code@param4

And try several in various ways and still can not.

They know if you can do this?? and how serious the regular expression.

I'm handling PL / SQL

Answer 1

This also uses a back reference, and works for your example:

select REGEXP_REPLACE('SELECT @param1, column2, column3, @param2 FROM dual WHERE @code = code_table AND amount > @param4 + 50',
  '[^@]?(@[[:alnum:]]+)?', '\1')
from dual;

REGEXP_REPLACE('SELECT@PAR
--------------------------
@param1@param2@code@param4

The same thing works from PL/SQL (as does @boneists of course):

set serveroutput on
DECLARE
    vl_result VARCHAR2(1000);
BEGIN
    vl_result := REGEXP_REPLACE('SELECT @param1, column2, column3, @param2 FROM dual WHERE @code = code_table AND amount > @param4 + 50',
        '[^@]?(@[[:alnum:]]+)?', '\1');
    dbms_output.put_line(vl_result);
END;
/

PL/SQL procedure successfully completed.

@param1@param2@code@param4