REGEXP_REPLACE query oracle

Question

I have a text like following.

    ( ( tt.TRAN_TYPE IN
       ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1332 )
   AND tt.CONTRACT IN
       ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1333 )
   AND tt.CUSTOMER_STATE NOT IN
       ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1334)
   AND tt.COT_IND IN
       ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1335 ))
OR     ( tt.TRAN_TYPE IN
        ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1336 )
   AND tt.WHLSE_COT NOT IN
       ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1337 )
   AND tt.COT_IND IN
       ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1338 )) )

I need to replace tt.* with condition tt.* IS NOT NULL and tt.*... as below.

( 
          ( (tt.TRAN_TYPE IS NOT NULL AND tt.TRAN_TYPE IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1332 ))
          AND (tt.CONTRACT IS NOT NULL AND tt.CONTRACT IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1333))
          AND (tt.CUSTOMER_STATE IS NOT NULL AND tt.CUSTOMER_STATE NOT IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1334))
          AND (tt.COT_IND IS NOT NULL AND tt.COT_IND IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1335)))
        OR ( (tt.TRAN_TYPE IS NOT NULL AND tt.TRAN_TYPE IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1336))
          AND (tt.WHLSE_COT IS NOT NULL AND tt.WHLSE_COT NOT IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1337))
          AND (tt.COT_IND IS NOT NULL AND tt.COT_IND IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1338))) 
          )

Please provide REGEXP_REPLACE or Any other trick if available in ORACLE.

NOTE : To me main challenge seems to be replace such a way that query order remains proper (i.e. opening and closing brackets placing properly)

score 0 · Answer 1 · answered Nov 10 '17 at 10:15

0

Try this.

SELECT REGEXP_REPLACE (yourtext, 'tt\.(.+) ', 'tt.\1 IS NOT NULL AND tt.\1 ')
  FROM yourtable;

answered Nov 10 '17 at 10:15

Kaushik Nayak

30,772
5
32
45

This will only add not null condition. However, I need to add enclosing bracket as well to make sure query order is not impacted as it contains multiple AND/OR. – Kalpit Joshi Nov 10 '17 at 11:07
What I did till now is following. select regexp_replace('( tt.TRAN_TYPE IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1332 ) AND tt.CONTRACT IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1333 ) AND tt.CUSTOMER_STATE NOT IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1334) AND tt.COT_IND IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1335 )) )','(tt.)([^ ]+)([[:alpha:]|[:blank:]|[:space:]|[:punct:]]*)','(\1\2 IS NOT NULL AND \1\2\3)' ) from dual; Now what I need is to add closing bracket after digits. – Kalpit Joshi Nov 10 '17 at 11:08
Below query l works. `select regexp_replace('( tt.TRAN_TYPE IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1332 ) AND tt.CONTRACT IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1333 ) AND tt.CUSTOMER_STATE NOT IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1334) ) OR ( tt.TRAN_TYPE IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1336 ) AND tt.WHLSE_COT NOT IN ( SELECT RULE_VALUE FROM GP_RULE_VALUE WHERE RULE_ID=1337 ) )','(tt.)([^ ]+)([[:alpha:]|[:blank:]|[:space:]|[:punct:]]*)([0-9]+)','(\1\2 IS NOT NULL AND \1\2\3\4)' ) from dual; ` – Kalpit Joshi Nov 10 '17 at 11:18

REGEXP_REPLACE query oracle

1 Answers1