Re: Automatic handling of quotes in inesrt statement

From: kenneth d atkins, s=david m thomas <Kenneth.D.Atkins_at_tek.com>
Date: 23 Dec 1994 21:36:23 GMT
Message-ID: <3dffsn$f8m_at_goodnews.wv.tek.com>

I have done this before, and I have had success with using the REPLACE function in the script that writes the INSERT statements. Example:

select 'INSERT INTO EMP VALUES('||empno||','''||

       replace(ename,'''','''''')||','||...

would produce

INSERT INTO EMP VALUES(1234, 'Mc''Govern', ...)

which would then insert correctly back into the table.

Hope this helps.

---

Kenneth Atkins
Financial Data Systems Incorporated (FDSI) Email: Kenneth.D.Atkins_at_tek.com

---

gagnon_at_dsr.com (Lorrie Gagnon) wrote:

>

> I am creating scripts that will SELECT data from each table in our
> database and create the INSERT statements that would be needed to
> rebuild the database. The problem that I am currently having is that
> some of my varchar2 fields have single quotes in them. When I create
> the insert statement, I get something like this:
>
> INSERT INTO EMP values (1234, 'Mc'Govern', ...)
>
> because Mc'Govern had a quote in his name.
>
> My question: is there a generic way that I can "escape" the quotes, so
> that I don't have to go thru with an editor or awk to get rid of them? Using
> a decode didn't work, since it is not known how many single quotes could
> possibly exist in a single text string.
>
> Thanks in advance,
> Lorrie Gagnon
> Digital Systems Research, Inc.
> gagnon_at_dsr.com
>
  Received on Fri Dec 23 1994 - 22:36:23 CET