SQL: Add single quotes, double quotes or string around a query result value

Lets say you have a Fruit table.

name        price          desc
----------   ---------      ----------------
Apple       1.94           Very popular
Mango      2.05           Popular in Summer

Now, you want to list the fruits and their descriptions and the descriptions be printed with single or double quotes around them. You can either use the string quoting literals introduced in 10g or by concatenating the quotes.

The string quoting literal is to put your string in q'[your string]'.  The square brackets can be replace by { }, < >,  ( ), or ! !.

1. Single quote

SQL> select name, q'[']' || desc || q'[']' from fruit;
or
SQL> select name, q'!'!' || desc || q'<'>' from fruit;
or
SQL> select name, '''' || desc || '''' from fruit;

The '''' is four single quotes

2. Double quote

SQL> select name,  q'["]' || desc || q'["]' from fruit;
or
SQL> select name, q'(")' || desc || q'{"}';
or
SQL> select '"' || desc || '"' from fruit;

The '"' is a double quote surrounded by single quotes.

3. String

Now, you want to add a string "Popularity: " in front of the descriptions.

SQL> select name, q'{Popularity: }' || desc from fruit;
or
SQL> select name, 'Popularity: ' || desc from fruit;

--------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.