Recipe6.2.Embedding Quotes Within String Literals

Recipe 6.2. Embedding Quotes Within String Literals

Problem

You want to embed quote marks within string literals. You would like to produce results such as the following with SQL:

 QMARKS -------------- g'day mate beavers' teeth ' 

Solution

The following three SELECTs highlight different ways you can create quotes: in the middle of a string and by themselves:

 1 select 'g''day mate' qmarks from t1 union all 2 select 'beavers'' teeth'    from t1 union all 3 select ''''                 from t1 

Discussion

When working with quotes, it's often useful to think of them like parentheses. When you have an opening parenthesis, you must always have a closing parenthesis. The same goes for quotes. Keep in mind that you should always have an even number of quotes across any given string. To embed a single quote within a string you need to use two quotes:

  select 'apples core', 'apple''s core',         case when '' is null then 0 else 1 end   from t1  'APPLESCORE 'APPLE''SCOR CASEWHEN''ISNULLTHEN0ELSE1END  ----------- ------------ -----------------------------  apples core apple's core                             0 

Following is the solution stripped down to its bare elements. You have two outer quotes defining a string literal, and, within that string literal you have two quotes that together represent just one quote in the string that you actually get:

  select '''' as quote from t1 Q - ' 

When working with quotes, be sure to remember that a string literal comprising two quotes alone, with no intervening characters, is NULL.