How escape apostrophe in email using REPLACE in Oracle

When you want to migrate data from one database to another database there might be several options available, but if you do not have any such tool and your data set is not too large best possible was is to generate INSERT/UPDATE..etc statements using string manipulation.

Lets say you need to get all the email addresses from database D1 and table T1, and update database D2 and table T2. assuming table t1 has columns t1.id and t1.email and in table t2 matching columns will be t2.id and t2.email, and also t1.id = t2.id

So how to generate UPDATE statements for that

So when we run the above code we will get results like

All above are good in best case scenario, but its really not

How about this one

And oracle will give following error when try to run this on target database

Solution

Haa …

REPLACE is the solution and here is how it done

and the generated value will be like