How to anticipate and avoid a single quote "in oracle
Suppose I have a column value like aaa'gh
it will throw an error in oracle saying the sql command has not finished.
My question is, if I don't know how much '
of my value, how can I safely avoid them.
The best way is to use quoting a string literal . Syntax q'[...]'
where the characters "[" and "]" can be any of the following, if they do not already appear on the string.
- []
- {}
- ()
- <>
You don't have to worry about single quotes inside a string.
Suppose the column value is aaa'gh
So, you can just write SQL as
SELECT q'[aaa'gh]' FROM DUAL;
This saves a lot of time for developers. Gone are the days when we (the developers) used to validate dynamic sql using dbms_output in the development db to make sure everything was in place before going into production.
My simple approach with this problem has always been to use
replace(string, '''', '''''')
but Lalit Kumar B's solution seems to be more complicated