Single quote manipulation Oracle

Most programming language will have a string terminator. Single quote(”) is the string terminator in Oracle.
You might have faced some issues with single quote while handling lot of string data and dynamic queries,cursors etc.How to effectively manipulate single quote in Oracle ? There are few tricks/workarounds

1. Traditional or trivial way. Append with another single quote

SQL> select ‘it”s a rainy day’ as trivial from dual;

it’s a rainy day

Below pl/sql block inserts a record with entry having single quote

insert into emp(empno,ename,location) values(10,’Larry John’,’Côte d”Ivoire’);

2. Using chr() function. We can use chr(39) which is an equivalent od single quote. Wherever you need to use single quote explicitly replace it with chr(39). This creates neatly code.

SQL> select chr(39) as single_quote from dual;


Below pl/sql blocks prints a dynamic sql string

v_quote  varchar2(1) := chr(39);
v_sql    varchar2(4000);
v_ename  varchar2(40);
v_ename  := ‘mark’;
v_sql := ‘select empno from emp where ename like ‘ || v_quote || ‘%’ || v_ename || ‘%’ || v_quote ;
select empno from emp where ename like ‘%mark%’

PL/SQL procedure successfully completed.

3. Using q function. It is a new feature added in Oracle 10g onwards and very handy.

Syntax :-
q'[string here]’ ( [] is delimiter here. Oracle will conside character immedeately after single quote is the delimiter )
It is a good practice to use delimiter from among [],{},().

SQL> select q'[it’s a rainy day]’ as q_function from dual;

it’s a rainy day

dbms_output.put_line( q'(it””s a rainy day)’ ); — deleimter ()
dbms_output.put_line( q’%it””s a rainy day%’ ); — delimter  %%
dbms_output.put_line( q’+Some special characters –> ~`!@#$%^&*()’”/;., +’ ); — delimter  +

it””s a rainy day
it””s a rainy day
Some special characters –> ~`!@#$%^&*()’”/;.,

PL/SQL procedure successfully completed.

There are some others dirty tricks too, like replacing single quote with ` ( Character found in keyboard just below od ESC key )
, replacing single quote with space etc.

Any other way to handle single quote ? Please share.

One response to “Single quote manipulation Oracle”

  1. Yes.. You are wright Single quote(”) is the string terminator in Oracle…. Very nice way you mentioned with example… Very good….