SQL : 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.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table