SQL: Handle / dealing single quote / apostrophe in string

String values in a SQL is surrounded by a pair of single quotes. A single quote either starts or ends a string value. If your string has single quote in it , you need to handle it appropriately for it to work.

1.  Replace single quote contained in a string by a pair of single quotes. For example,

      SELECT * FROM FRUIT WHERE note = 'Product of Smith''s farm';
      Product of Smith's farm is replaced with Product of Smith''s farm.

      SELECT * FROM FRUIT WHERE note = '''Honey'' sweet type';
      'Honey' sweet type is replaced with ''Honey'' sweet type.

      SELECT * FROM FRUIT WHERE note = 'Product of Thomsons''';
      Product of Thomsons' is replaced with Product of Thomsonss''.


2. Single quote in string concatenation.

     SELECT 'Fruit''s name: ' || '''' || NAME || '''', PRICE FROM FRUIT;

     will return something like: Fruit's name: 'Apple'       $3.99
                                                Fruit's name: 'Orange'     $1.99


3. Replace single quote contained in a string with chr(39). For example,

     SELECT * FROM FRUIT WHERE note = 'Product of Smith' || chr(39) || 's farm';

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

                        
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.