Oracle: quotes and quote operator

There are several methods to put the quote into the string.

The first (and very traditional) one: use 2 quotes.
[cc lang=”oracle8″]
select ‘This ” is quote’ from dual;
[/cc]
If there are more than one quote, it’s difficult to read and write such strings

The second: use chr(39)
[cc lang=”oracle8″]
select ‘This ‘ || chr(39) || ‘ is quote’ from dual;
[/cc]

The third: put the quote into the variable
[cc lang=”oracle8″]
declare
s_quote varchar2(1) := ”” ;
begin
dbms_output.put_line(‘This ‘ || s_quote || ‘ is quote’ ) ;
end;
[/cc]

And, finally, Oracle 10g has added new feature: quote operator.
[cc lang=”oracle8″]
select q'[This ‘ is quote]’ from dual ;
[/cc]

The general form is q’X string X’. Here X is just some character. If the brackets are used, Oracle expects the closing bracket for the end of the string.

Here are the additional examples:
[cc lang=”oracle8″]
select q'(This ‘ is quote)’ from dual ;
select q’|This ‘ is quote|’ from dual ;
select q’#This ‘ is quote#’ from dual ;
select q’#This ‘ is quote#’ from dual ;
select q’?This ‘ is quote?’ from dual ;
select q’TThis ‘ is quoteT’ from dual ;
[/cc]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.