Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, bhabani.

Asked: September 20, 2003 - 9:02 pm UTC

Last updated: September 16, 2008 - 11:44 am UTC

Version: 8.1.7.4

Viewed 50K+ times! This question is

You Asked

Hi Tom

I have this interesting observstion on SQL*Plus:

SQL> select * from t1 where name like 'j%\%%' escape '\';

A NAME
---------- ---------------
23 jamshia%gan
45 jamshia%kid

SQL> set escape '\';
SQL> select * from t1 where name like 'j%\%%';

A NAME
---------- ---------------
10 jamshid
10 jamshid
23 jamshia%gan
45 jamshia%kid
67 jamshid*jid

SQL> select * from t1;

A NAME
---------- ---------------
10 jamshid
10 jamshid
23 jamshia%gan
45 jamshia%kid
67 jamshid*jid


Why SETting ESCAPE is not working properly on SQL* Plus

Thanks and Regards
BSPradhan


and Tom said...

because "set escape", the sqlplus command, has NO EFFECT whatsoever on SQL.

it is for totally different purposes. "set escape" is used to escape the substitution character in sqlplus -- it has NO effect on SQL processing. consider:


ops$tkyte@ORA920> set escape \
ops$tkyte@ORA920> select '\&1' from dual;

'&
--
&1

see, plus didn't ask me for the value of &1, nor did plus pass the \ onto SQL itself -- we escaped the & in that query... putting escape back off as normal:

ops$tkyte@ORA920> set escape off
ops$tkyte@ORA920> select '\&1' from dual;
Enter value for 1: fda
old 1: select '\&1' from dual
new 1: select '\fda' from dual

'\FD
----
\fda



so, escape worked as designed in sqlplus.

don't forget -- sqlplus is NOT sql -- sqlplus is a simple command line tool that you or I could write ourselves. It has nothing to do with SQL really -- it is just a rudimentary interface that lets us type in dynamic sql and have it execute.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

b, September 21, 2003 - 3:25 pm UTC

I donot have any other question related to that.

Regards

A Reader, September 21, 2003 - 10:12 pm UTC

Then how could we do the same (escape) in sql queries?

Tom Kyte
September 22, 2003 - 7:25 am UTC

you cannot do the "same" escape in SQL since it is SQLPlus that does "substitution" (so there is no need for the "same" escape in SQL)

There is an escape in SQL -- for escaping the meaning of % and _ in a LIKE predicate. And it is demonstrated in the original question above.

Escaping for SQL-Engine

McJan, September 22, 2003 - 4:23 am UTC

Hi (just copy and past from my own investigations ...)
There are 2 different ESCAPE's, one in the SQL*PLUS shell and one for
the SQL Engine. Both have nothing to do with each other.

1. SQL*PLUS Shell
-----------------

....

2. SQL Engine
-------------

Now the second part, the SQL Engine.

- Searching for rows in tables with an equal (=) operator
The column from the table have to match exactly the search string you submitted.

... where s = 'D&W';

set escape Ö
select 'ÖkkÖ&' my, i, s from b1 where s = 'DÖ&W';

MY I S
--- ---------- ------------------------------
kk& 5 D&W

- like searching

The like operator provides a speacial handling of wildcards.
'%' matches any string, '_' a single character.

select 'ÖkkÖ&' my, i, s from b1 where s like 'DÖ&W';
->
MY I S
--- ---------- ------------------------------
kk& 5 D&W

2 new rows:
- A_B\C%D
- AKBO

select * from b1 where s like 'A_B%';

I S
---------- ------------------------------
3 A_B\C%D
4 AKBO

Because '_' matches any single character, column 3 and 4 will be found.
But if you want to suppress the wildcard and exactly want the rows
starting with an 'A_' followd by any string, you need to ESCAPE the '_'
wildcard by a self defined escape character other then '%' and '_':

select * from b1 where s like 'A\_B%' ESCAPE '\';

I S
---------- ------------------------------
3 A_B\C%D

select * from b1 where s like 'AP_B%' ESCAPE 'P';

I S
---------- ------------------------------
3 A_B\C%D

3.) Examples:

set escape Ö
drop table b1;
create table b1 (i number(2), s varchar2(30));
insert into b1(i,s) values(1,'trias\-thieme\, hippokrates\');
insert into b1(i,s) values(2,'updated');
insert into b1(i,s) values(3,'A_B\C%D');
insert into b1(i,s) values(4,'AKBO');
insert into b1(i,s) values(4,'A\_BL');
insert into b1(i,s) values(5,'DÖ&W');
insert into b1(i,s) values(6,'Jan O''Conner');
select * from b1;


select * from b1 where s like 'A_B%';
select * from b1 where s like 'Jan ''O%';
select * from b1 where s like 'Jan O'%';
select * from b1 where s like 'Jan O''%' or s = 'A\_BL';
select * from b1 where s like 'Jan O''%' or s = 'DÖ&W';


Escape ( Multiple )

A reader from india, September 23, 2003 - 5:56 am UTC

How to use {} as the escape for any where clause like operator

eg: select * from a where ref like '%\%%' escape '\';

like above how we can do it for

select * from a where ref like '%{%_@...}...

please let me know. I did search in your web site and also in OTN but i didnt get an answer.

Thanks in advance

Tom Kyte
September 23, 2003 - 6:41 am UTC


you cannot, that is not the syntax in SQL.

you would query

like '%\%\_@.....'



OK

John, November 21, 2003 - 1:40 am UTC

Dear sir,
Is there any 'chr' sequence for tab(one space between each
character) like the one we have chr(10) for newline?

Tom Kyte
November 21, 2003 - 4:45 pm UTC

chr(9)

escape character and nvarchar2

Andy, January 14, 2004 - 2:39 am UTC

Tom, I don't understand this behaviour:

mires@linux2-l2ifag> create table y(vc varchar2(100), nvc nvarchar2(100));

Tabelle wurde angelegt.

mires@linux2-l2ifag> insert into y values ('ab/c', 'xy/z');

1 Zeile wurde erstellt.

mires@linux2-l2ifag> select count(*) from y where vc like 'ab//%' escape '/';

COUNT(*)
----------
1

mires@linux2-l2ifag> select count(*) from y where nvc like 'xy//%' escape '/';
select count(*) from y where nvc like 'xy//%' escape '/'
*
FEHLER in Zeile 1:
ORA-01425: Escape-Zeichen muss eine Zeichenfolge der Länge 1 sein

mires@linux2-l2ifag> select count(*) from y where nvc like 'xy//%' escape N'/';

COUNT(*)
----------
1

I've read in the 9i SQL reference that Oracle automatically converts my escape character into the same data type as the column I am LIKE-ing, but why does that mean that my escape character '/' is "lost", but N'/' isn't?

Escape CHR(10)

Bhasker Masna, September 10, 2008 - 6:42 pm UTC

Hi Tom,
Thanks for explanation of escape character. But i have a strange problem of data inside one column having new line between text. And when i try to do pattern matching using LIKE clause i would like to ignore new line inside the column and match data with other column.But escape CHR(10) is not working as expected.
Any ideas what is wrong with queries below?
--create dummy table
create table b1 (col1 varchar2(200), col2 number)

--insert some text which has new line inside the text
insert into b1
select 'insert ' || chr(10) ||'into table' , 1 from dual

--Now try to match string
select * from b1 where col1 like 'insert%' -- works
COL1 COL2
insert
into table 1

select * from b1 where col1 like 'insert into table' escape chr(10)
-- NO ROWS RETURNED , Here escaped with CHR(10) , even then why data is not returned??-- is it problem with ESCAPE?

select * from b1 where trim(chr(10) from col1) like 'insert into table'
-- NO ROWS RETURNED

select * from b1 where col1 like 'insert ' ||CHR(10) || 'into table'
-- 1 ROW RETURNED
--Strange error message when i run below query.
select * from b1 where 'insert into table' like col1 escape chr(10)
error: ORA-01424:missing or illegal character.

Can you please help.
Thanks a lot
Bhasker
Tom Kyte
September 16, 2008 - 11:44 am UTC

you have misinterpreted what "escape" is all about.

Normally, % and _ are "special" if you wanted to search for a string that contains % or _, you need to ESCAPE % and _ in the search string.

Eg,

ops$tkyte%ORA10GR2> select * from t;

X
---------------
hello%world
hello_world
hello world

ops$tkyte%ORA10GR2> select * from t where x like 'hello%world';

X
---------------
hello%world
hello_world
hello world

<b>returns all rows because % is a wildcard matching 0, 1 or more characters, it is not a percent, it is a WILDCARD</b>

ops$tkyte%ORA10GR2> select * from t where x like 'hello_world';

X
---------------
hello%world
hello_world
hello world

<b>returns all rows because _ is a wildcard matching 1 character, it is not an underscore, it is a wildcard character</b>

ops$tkyte%ORA10GR2> select * from t where x like 'hello\%world' ESCAPE '\';

X
---------------
hello%world

<b>Now we have ESCAPED the % in the search string, now % in hello\%world means percent - not wildcard - that is what escaping does, it makes special characters "not special"</b>

ops$tkyte%ORA10GR2> select * from t where x like 'hello\_world' ESCAPE '\';

X
---------------
hello_world

<b>same as above</b>



you might want to replace chr(10) with nothing - you cannot use trim - trim only works on the ENDS of the string, not in the middle.