CODEWITHSUNDEEP
sqloracle-database
chikkada
chikkada

Reputation: 75

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.

Upvotes: 1

Views: 5848

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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.

Upvotes: 14

a.j. tawleed
a.j. tawleed

Reputation: 904

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

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

but Lalit Kumar B solution, seems more sophisticated

Upvotes: 8

Related Questions