How can I simplify this statement using regular expressions?
Please suppose you have the following statement stored in a VARCHAR2:
UPDATE PLUTO SET FIELD1='',FIELD2='',FIELD3='TIGER',FIELD4='',FIELD5='SCOTT';
I would like to write a regular expression that simplifies the previous statement, by removing all the updates setting to NULL, because they are useless (NULL is the default statement), as follows:
UPDATE PLUTO SET FIELD3='TIGER', FIELD5='SCOTT';
Could you help me?
I have never been so good with regular expressions...!!
SELECT REGEXP_REPLACE( REGEXP_REPLACE( 'UPDATE PLUTO SET FIELD1='''',FIELD2='''',FIELD3=''TIGER'',FIELD4='''',FIELD5=''SCOTT'',FIELD9='''';', '(FIELD[0-9]+=(?'''',*))','') , '[, ]+;',';') from dual
how's this? it will remove all FIELD#='' (with or without final ,)
here's a regular expression cheat sheet: http://psoug.org/snippet.htm/Regular_Expressions_Regex_Cheat_Sheet_856.htm?PHPSESSID=7238be874ab99d0731a9da64f2dbafd8 and good resource on Oracle's Regular Expression http://psoug.org/snippet/Regular-Expressions---REGEXP_REPLACE_882.htm