Monday, November 27, 2006

No need for doubled quotation marks anymore

Before Oracle 10g, single quotes in a string literal had to get doubled (or even tripled in some cases when dynamic sql was involved).

Task: define the string Thomas' brother said: 'I'm the brother of Thomas.' as a string literal.

Pre Oracle 10g solution:
select 'Thomas'' brother said: ''I''m the brother of Thomas.'''
from dual;
Oracle 10g solution:
select q'#Thomas' brother said: 'I'm the brother of Thomas.'#'
from dual;
Using the q quotation syntax it is now much easier to define string literals containing single quotes. Instead of the hash symbol in the example, any other symbol that does not appear within the literal can be used (except space, tab, and return). If you use any of "[{<(" for the opening quote delimiter, the according closing bracket "]}>)" must get used.

No comments: