Clever Geek Handbook

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.

+1


source to share


2 answers


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.

+13


source


My simple approach with this problem has always been to use

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


but Lalit Kumar B's solution seems to be more complicated

+6


source






More articles:


All Articles