Embedding Single Quotes Inside a Literal String

posted Apr 26, 2012, 4:33 PM by Sachchida Ojha   [ updated Apr 26, 2012, 4:34 PM ]
Until Oracle 10 you would write 2 single quotes next to each other if you want the string to contain a single quotes in that position. here are some examples

Literal
select 'There''s nothing like DBAREF website for Oracle DBA,s' from dual;
Actual value
There's nothing like DBAREF website for Oracle DBA,s

SQL> select 'There''s nothing like DBAREF website for Oracle DBA,s' from dual;

'THERE''SNOTHINGLIKEDBAREFWEBSITEFORORACLEDBA,S'
----------------------------------------------------
There's nothing like DBAREF website for Oracle DBA,s

Literal
select '"I am the best"' from dual;
Actual value
"I am the best"
SQL> select '"I am the best"' from dual;

'"IAMTHEBEST"'
---------------
"I am the best"

SQL>
==========================================================
Literal                                        Actual value
==========================================================
''''                                                '
'''Hello'''                                        'Hello'
''''''                                                ''
==========================================================
As you see you need 6 Single quotes to designate a literal containing 2 consecutive single quotes. Oracle database 100g and later introduced user-defines delimiters. Start the literal with "q" to mark your delimiter and surround your delimeter with Single quotes.

For example

 Literal Actual value
 q'(There's nothing like DBAREF website for Oracle DBA,s)' There's nothing like DBAREF website for Oracle DBA,s
 q'("I am the best")' "I am the best"
 q'(')' '
 q'('Hello')' 'Hello'
 q'('')' ''

SQL> select  q'(There's nothing like DBAREF website for Oracle DBA,s)' from dual;

Q'(THERE'SNOTHINGLIKEDBAREFWEBSITEFORORACLEDBA,S)'
----------------------------------------------------
There's nothing like DBAREF website for Oracle DBA,s
SQL> select q'(')' from dual;

Q
-
'
SQL> select q'('Hello')' from dual;

Q'('HEL
-------
'Hello'

SQL>






'




Comments