Re: Replacing the pesky '

From: <Simon.Anderson_at_scisys.co.uk>
Date: Mon, 22 Oct 2001 08:58:24 -0700
Message-ID: <F001.003B16C8.20011022091520@fatcity.com>

Could be worse - could be raining,

Replacing those awkward characters comes down to distinguishing between them used as quotes and used to search for that character.

The sql functions ASCII() and chr() both help here:

SQL > select ascii('''') from dual;

ASCII('''')

---

         39

The four single quotes explain that you're trying to quote a single quote. I have to admit it's awkward, but it works. Once you know that the ascii value of the ' character is 39, you can use it instead:

SQL > select 'O'||chr(39)||'Reilly' from dual;

'O'||CHR

---

O'Reilly

(You could use the four-single-quotes thing, but it's clumsy.)

Finally, you can plug the ascii character value into your REPLACE statement, something like:

SQL > select replace('O'||chr(39)||'Reilly', chr(39), '*') from dual;

REPLACE(

---

O*Reilly

And this all works just fine, until someone decides to change their name to four  single quotes...

Anyone know a better way of coping with this kind of 'problem' data ?

Simon Anderson

>
> Gotta love Mondays,
>
> We've been through this before, and I usually don't have a problem, but
> today my brain must think it's the weekend.
>
>
> Hey, I've got a bunch of names like O'Brien and O'Reilley where I need to
> replace the ' symbol. I've tried every type of combination of single
> quotes and slashes(escape sequence) I can think of, but I must have missed
> one, because I can't get it to replace.
>
> Any Ideas? Here's the progenitor of the whole mess.
>
> 1* select replace(lastname,' ' ') from adst where lastname like 'O%'
> SQL> /
> ERROR:
> ORA-01756: quoted string not properly terminated

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Simon.Anderson_at_scisys.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Oct 22 2001 - 10:58:24 CDT