How To: Insert Data With Single Quotes In Oracle

[Oracle] Entering Data With Single Quotes

Oracle does not allow single quote (') as a part of data by default. The reason for this is that Oracle takes in all sting data in single quotes.

Eg. SQL> INSERT INTO Student (stud_id, stud_name, stud_address,stud_gaurdian,stud_contact,stud_admission_date)
VALUES (4857, 'John Miller' , 'Forbury Road', 'Stuart Miller', 9096232348, to_date('1/31/2010', 'MM/DD/YYYY'));
Therefore we need to explicitly tell Oracle that the single quote we are trying to enter is a part of data and not the syntax itself.
 

:: Soulution ::
We enter one more single quote every time we wish to enter a single quote as part of the data.
Eg.Data                   Expression
     Penny's               'Penny''s'
     'John','Vishal'      '''John'',''Vishal'''
     'Shweta's'           ''Shweta''s''

SQL> INSERT INTO Address (stud_id, house_name, street-name, landmark, po_box, state)
VALUES (4857, 'Miller''s Villa' , '''John Nash Street''', 'Christ''s Church', 440023, 'MAS')

The above statement will insert data as below

4857     Miller's Villa       'John Nash Street'      Christ's Church       440023      MAS
 
  Summing up, the only rule to be followed while entering data with single quotes is that we should append one more single quote just before it!