Quoting inside literal strings

Well some of you may have noticed my slight boo-boo yesterday. I had made some notes on a blog post I planned to finish later, and accidentally published instead of just saving to continue later - so here's my second attempt ;-)

What I found funny, was the post was about exactly what Steven Feuerstein implemented for yesterdays (4/11/2010) PL/SQL Challenge - embedding quotes in literals.

I was reminded of it recently while searching for something - I can't remember what for, but I came across this forum entry: http://database.itags.org/oracle/81577/

I used what I thought an interesting method for adding single quotes to strings - interesting I suppose because I've never used this method before:
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=' || chr(39)||'DD.MM.YYYY'||chr(39);

The single row function CHR() will return the character equivalent to the numeric passed, in my case the ASCII character for single quotes. I the past I've mainly used CHR to provide my line feeds and carriage returns. (chr(10 & chr(13) respectively)

The author used this method because inserting a single quote in a normal string means you need to escape it, and this sometimes looks ugly or difficult to interpret:
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT='''DD.MM.YYYY''';

Oracle 10g introduced a method often called q-quote notation, further information can be found in the SQL Reference documentation here.

This format basically allows you to type the string as you would normally expect to see it, so the following example would display everything between the brackets [ ]
q'[ this isn't my string, is it? ]'

Which would mean our previous example would look like:
EXECUTE IMMEDIATE q'[ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY']';

Of course, as the documentation describes, you don't need to use those particular brackets. For a while I thought the brackets had to be curly { }, which was frustrating because it became annoying to type. Until recently, I wasn't aware you could go beyond any form of brackets and use any character - as long as it matched up. Lucky, considering yesterday's PL/SQL Challenge did just that!

I found it uncanny, since the list of examples I had lined up for this post was very similar to Steven's list - these will all produce the same result - Hello Scott's computer:

select 'Hello Scott''s computer' hello from dual; 

select 'Hello Scott'||chr(39)||'s computer' hello from dual; 

select q'[Hello Scott's computer]' hello from dual;
select q'{Hello Scott's computer}' hello from dual;
select q'<Hello Scott's computer>' hello from dual;
select q'(Hello Scott's computer)' hello from dual;
select q'"Hello Scott's computer"' hello from dual;
select q'sHello Scott's computers' hello from dual;

So I would suggest choose a combination of brackets that makes sense to you - probably not the last example since it's not that intuitive.

Someone asked me what the point of this particular notation was. I've found it very handy in the past for a number of reasons, but it probably still depends on what your brain is used to seeing (I'm still quite happy using DECODE over CASE, for instance)

For example, when it comes to replacing quotes with double quotes, an exercise I did for something long ago, I can either write it as this complicated list of quotes:
with mine as (select q'[Hello Scott's computer]' hello from dual)
select replace(hello, '''', '"') from mine;

Or make it a little more elegant. This is still probably a weak example, but I've seen a horrible list of quotes in my time that took a while for me to count and realise what was doing what.
with mine as (select q'[Hello Scott's computer]' hello from dual)
select replace(hello, q'[']', '"') from mine;

And then there is the case of large dynamic SQL. It truly is horrible to have to apply two single quotes every time a quote is required, particularly when you're conditionally concatenating certain clauses; and want to just copy and paste the query directly in your GUI to test without replacing all the quotes again.
Here is a basic example of how a small bit of dynamic SQL could become much more readable with the q-quote notation:

declare
  my_string varchar2(100);
begin
  execute immediate q'(select dummy from dual where dummy = 'X')'
  into my_string;
end;
/

I think this PL/SQL challenge has turned out to be a great boon for Oracle developers worldwide. You may be quite a proficient PL/SQL programmer, but not everyone has had the opportunity for exposure to all facets of the database, and people are reporting how wonderful it is to pick up these tips and tricks - in the arsenal ready for future use.