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;

TRIVIAL
—————-
it’s a rainy day

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

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

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;

S

Below pl/sql blocks prints a dynamic sql string

declare
v_quote  varchar2(1) := chr(39);
v_sql    varchar2(4000);
v_ename  varchar2(40);
begin
v_ename  := ‘mark’;
v_sql := ‘select empno from emp where ename like ‘ || v_quote || ‘%’ || v_ename || ‘%’ || v_quote ;
dbms_output.put_line(v_sql);
end;
/
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;

Q_FUNCTION
———-
it’s a rainy day

Begin
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  +
end;
/

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.

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

1 thought on “Single quote manipulation Oracle”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s