Advance Replace in insert scripts

Question

I have below 2 insert statements which i took the export from sql developer from dev environment. I have delete those records from dev afterwards. Now i want to run this insert statement again in dev because those are my back up but i am getting error as virtual column which is ORD_DAYID cannot be used inside insert script. So i want to exclude this column and also the respective values using replace function or any tools which i dont know. I didnt know previously that i have virtual column for this table. I would like to know is there any tool or function where i can select ORD_DAYID and also the respective values get selected and then i can delete those and then i can be able to run this insert statement again in test enviornment.

P.S i have mentioned only 2 sample insert statements but there are 1000 insert statements. So its very difficult to manually delete this ORD_DAYID from this insert statements with respective values.

Insert into test_ord (IS_GRP,ORD_DAYID,REF_CAMPA_CODE) values (1,20150813,null);
Insert into test_ord (IS_GRP,ORD_DAYID,REF_CAMPA_CODE) values (1,20150828,null);

Answer 1

You can edit your INSERT statements using regular expressions, in an editor such as Notepad++.

So to change this ...

Insert into test_ord (IS_GRP,ORD_DAYID,REF_CAMPA_CODE) values (1,20150813,null);

... into this ...

Insert into test_ord (IS_GRP,REF_CAMPA_CODE) values (1,null);

You need a search pattern of:

Insert into test_ord \(IS_GRP,ORD_DAYID,REF_CAMPA_CODE\) values \(([0-9]+),([0-9]+),null\);

and a replace pattern of:

Insert into test_ord \(IS_GRP,REF_CAMPA_CODE\) values \(\1,null\);

Obviously you will need to refine the search pattern to cater for all the different values of IS_GRP, and REF_CAMPA_CODE in your 1000 statements.

---

" is there any way where we can count the place of column and value and replace it with null"

No. The snag with virtual columns is that they cannot be referenced in INSERT or UPDATE statements. So you need to totally exclude it from the projection.

"i am not able to find those option in notepad++"

Really? Search and replace is not an exotic option:

• From the menu: Search > Find > Replace [tab] (or [ctrl]+h)
• As the search mode select the regular expression radio button