How to Code Single Quotes with Q-Quote

Starting in 10g, use a 'q', a pair of single quotes, and a pair of brackets to code a literal that uses a single quote. With this method, you can code it exactly as it is.

Select q'!He's ok!'        As Single_Quote_In_Middle
     , q'['I like him.']'  As Single_Quote_At_Ends
     , q'(')'              As Single_Quote_Alone
     , q'<''>'             As Two_Single_Quotes
  From Dual;

To delete or insert a name with a single quote:

Delete From Emp Where EName = q'\O'MALLEY\';

Insert Into Emp ( EmpNo , EName         , DeptNo )
         Values ( 7474  , q'{O'MALLEY}' , 10     );
Commit;

To search for a name with a single quote in it:

Select EmpNo, Ename, DeptNo
 From Emp
Where Ename Like q'[%'%]';

Prior to 10g:

Select 'He''s ok'         As Single_Quote_In_Middle
     , '''I like him.'''  As Single_Quote_At_Ends
     , ''''               As Single_Quote_Alone
     , ''''''             As Two_Single_Quotes
  From Dual;