How to anticipate and escape single quote ' in oracle

Suppose I have a column value as aaa'gh it will throw error in oracle saying sql command not properly ended.

My question is if I don't know how many ' are in my value, how can I escape them safely.

The best way is to use the quoting string literal technique. The syntax is q'[...]', where the "[" and "]" characters can be any of the following as long as they do not already appear in the string.

• !
• [ ]
• { }
• ( )
• < >

You don't have to worry about the single-quotation marks within the string.

Suppose i have a column value as aaa'gh

So you could simply write the SQL as,

SELECT q'[aaa'gh]' FROM DUAL;

It saves a lot of time for developers. Gone are those days when we(developers) used to verify the dynamic sql using dbms_output in development DB, just to make sure things are at place before moving into production.

My simple approach with this problem, was always to just use

replace(string, '''', '''''')

but Lalit Kumar B solution, seems more sophisticated