Documentation on the escape clause should be with the LIKE operator in the
SQL Reference; the escape keyword in SQL is used to make the Oracle server
special-quote wildcard-matching characters (percent signs and underscores.)

BTW, the solution below won't actually work in your case. There apparently
is some confusion over the Oracle SQL escape keyword, and the SQLPlus
replacement variable.

In the SQLPlus Oracle client program, the ampersand introduces a text
replacement variable. Hence if you type in:
update TABLE_NAME set
column_name = 'ABCDEFG'
where column_name = 'UVWX&Z';
SQLPlus will note that you have a text replacement variable called "Z" and
will ask you to type in a value for Z; assume that you type in QQQ, then
what SQLPlus will actually ship off to the Oracle server is:
update TABLE_NAME set
column_name = 'ABCDEFG'
where column_name = 'UVWXQQQ';
replacing the &Z with the QQQ that you typed in. There is another format of
replacement variables that are introduced by 2 ampersands: i.e. &&Z. The
difference between the single and double ampersand is a single ampersand
will always ask the user for input, whereas the double ampersand will ask
for user input ONLY if the variable has not yet been defined.

The solution to your problem is to make SQLPlus not "react" to the
ampersand. There are basically 3 different solutions:
1) Make the query so that the ampersand character never even shows up:
update TABLE_NAME set
column_name = 'ABCDEFG'
where column_name = 'UVWX' || chr(38) || 'Z';
2) Tell SQLPlus that the text replacement variable introduction token is
something other than ampersand:
set define @
This makes SQLPlus ignore ampersands, and instead will make it look for
at-signs.
3) Tell SQLPlus to ignore text replacement variables completely regardless
of the "define" token":
set scan off

HTH.

...Rudy


-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed]
Sent: Monday, June 30, 2003 3:38 PM
To: LazyDBA.com Discussion
Subject: Re: RE: Another Question


where is the documentation about escape characters? I use them in C, but
have never seen them in SQL?
>
> From: Randy Patterson <[Email Address Removed] Date: 2003/06/30 Mon PM 03:36:32 EDT
> To: "LazyDBA.com Discussion" <[Email Address Removed] Subject: RE: Another Question
>
> I think you need to use the escape option:
> WHERE last_name LIKE '%A\_B%' ESCAPE '\';
> Try 'UVWX\&Z ESCAPE \'
>
> Randy Patterson
> > [Email Address Removed] Anadarko Petroleum Corp.
> P.O. Box 1330
> Houston, TX 77251-1330
> (832)636-1655
>
>
>
>
> > -----Original Message-----
> > From: [Email Address Removed] [SMTP:[Email Address Removed] > Sent: Monday, June 30, 2003 2:30 PM
> > To: LazyDBA.com Discussion
> > Subject: Re: Another Question
> >
> > i think you need to do this
> >
> > 'UVWX''&''Z'
> >
> > try that. its going to be something with the quotes. Ive found keeping
> > track of quoting to be tedious.
> > >
> > > From: "Bill Ducker" <bill.[Email Address Removed] > > Date: 2003/06/30 Mon PM 03:27:45 EDT
> > > To: "LazyDBA.com Discussion" <[Email Address Removed] > > Subject: Another Question
> > >
> > > update TABLE_NAME set column_name = 'ABCDEFG'
> > > where column_name = 'UVWX&Z';
> > >
> > > This looks like it should be easy, BUT... it interprets the
> > ampersand ('&') in the string literal as some sort of variable, stops,
> > and issues this statement:
> > >
> > > Enter value for Z:
> > >
> > > How to I get around this problem?
Oracle LazyDBA home page