On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Saturday, March 14, 2009

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;

2 comments:

  1. I tried this script on a 10.1.0.5 database and just could not just it to work. For kicks, I took the same script and run it in a 10.2.0.4 database and it worked. I assumed the q-quote worked on any 10g database, but think the sub-versioning might be a little sensitive.

    ReplyDelete