There are several methods to put the quote into the string.
The first (and very traditional) one: use 2 quotes.
SELECT 'This '' is quote' FROM dual;
If there are more than one quote, it’s difficult to read and write such strings
The second: use chr(39)
SELECT 'This ' || CHR(39) || ' is quote' FROM dual;
The third: put the quote into the variable
DECLARE
s_quote VARCHAR2(1) := '''' ;
BEGIN
dbms_output.put_line('This ' || s_quote || ' is quote' ) ;
END;
s_quote VARCHAR2(1) := '''' ;
BEGIN
dbms_output.put_line('This ' || s_quote || ' is quote' ) ;
END;
And, finally, Oracle 10g has added new feature: quote operator.
SELECT q'[This ' IS quote]' from dual ;
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:
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 ;
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 ;
Leave a Reply