Re: SQL quoting question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 17 Jul 1998 20:16:45 GMT
Message-ID: <35b5b06f.20648611@192.86.155.100>

A copy of this was sent to "Richard J. Finn" <rfinn_at_hiwd.net> (if that email address didn't require changing) On Fri, 17 Jul 1998 13:42:26 -0500, you wrote:

>Okay, one quotes an apostrophe (') with adding another one (''),
>
> But how does one quote an ampersand (&), and underscore (_), and a
>percent sign (%). So far, doubling those or adding a preceding slash
>(\) doesn't seem to work.
>

well, the & is a sqlplus'ism only. To make that be just another character in sqlplus, simply issue:

SQL> set scan off

and & will be just like any other character.

The _ and % are special only when used in a LIKE statement. You can escape it with any character you like via the escape clause:

SQL> create table test ( data varchar2(25) ); Table created.

SQL> insert into test values ( 'HELLO_THERE' );
SQL> insert into test values ( 'HELLO THERE' );
SQL> insert into test values ( 'HELLO_%THERE' );

SQL> select * from test where data like 'HELLO_%T%';

DATA

---

HELLO_THERE
HELLO THERE
HELLO_%THERE SQL> select * from test where data like 'HELLO\_\%T%' escape '\';

DATA

---

HELLO_%THERE Hope this helps...

> Any ideas?
>
>Thanks,
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jul 17 1998 - 15:16:45 CDT