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.

SQL: select a unique/distinct subset of columns and also show the other columns - partial distinct / unique columns

For example, you have this FRUIT table in your database. (This is just an example, it contains repeating data that need to be normalized :-)).

Name          Price         Date                   Place
Apple          3.28          1/10/2015          Darloo
Apple          3.28           1/22/2015         Jumbo
Orange        2.50           1/15/2015         Darloo
Orange        2.50           1/19/2015         Jumbo

Lets say that you want to get unique Name and Price only but the result also show Date and Place. Some thing looks like this.

Apple          3.28          1/10/2015          Darloo
Orange        2.50           1/19/2015         Jumbo

If you use the SQL: SELECT unique Name, Price, Date, Place FROM FRUIT to query the table, you will get all the rows with repeating Name and Price.

You may use the following SQL to get the result you want.

SELECT Name, Price, Date, Place
FROM (
       SELECT FRUIT.*, row_number() over (partition by Name, Price order by Name)  as rn
       FROM FRUIT) FRUIT
WHERE rn = 1;

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

                        
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.