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?
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
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?
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
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.