RE: Use of the quote / apostrophe in text fields
- From: "Robson, Peter" <pgro@xxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Mar 2004 09:18:15 -0000
Thanks for that, guys -
Yes, been down that route, but it is not strictly germaine to the problem.
Nevertheless it is a useful technique which I have applied widely. Things
become interesting when you have up to a dozen quotes lined up on either
side of your data value, which can happen when the data value is itself a
quote (think about it...).
No, the problem here is when a field arrives for processing in an update
script, and is thus bounded by a quote at start and finish, but ALSO
contains an embedded quote. The parser scans down the line of characters,
encounters the second quote, assumes that is the end of the field - and all
hell breaks loose.
The answer (thank you Pete Sharman) is in 10g, where one can, so it seems,
re-define the text string delimiter. I'm just glad someone in Oracle saw fit
to address this particular point.
peter
edinburgh
> -----Original Message-----
> From: Mladen Gogala [mailto:mladen@xxxxxxxxxxxxxxx]
> Sent: Monday, March 15, 2004 6:01 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Use of the quote / apostrophe in text fields
>
>
> Or, using the double trouble quotes:
> insert into t1 values('A''MHOINE PSAMMITE');
>
> On 03/15/2004 12:54:07 PM, Daniel Fink wrote:
> > I don't know how much this would help, but you might be able to
> > use the CHR() function.
> >
> > SQL> insert into t1 values ('A'||chr(39)||'MHOINE PSAMMITE');
> >
> > 1 row created.
> >
> > SQL> select * from t1;
> >
> > QUOTE_STRING
> >
> --------------------------------------------------------------
> ------------------
> >
> > A'MHOINE PSAMMITE
> >
> >
> > "Robson, Peter" wrote:
> >
> > > Many of our data fields require use of a quote embedded
> > > within the text field, most particularly for proper names (eg
> > > 'A'MHOINE PSAMMITE', just to give you a taste of Scottish
> > > geological terminology!)This can prove troublesome where
> > > autogenerated update scripts are used, in which the value of
> > > the field is retrieved back into a quote-delimited string.Is
> > > anyone aware of a means of redefining the field delimiter from
> > > a quote to some other arbitrary character in
> > > SQL?thanks,peteredinburgh
> > >
> > >
> > >
> ********************************************************************
> > >
> > > This e-mail message, and any files transmitted with it, are
> > > confidential and intended solely for the use of the addressee.
> > > If
> > > this message was not addressed to you, you have received it in
> > > error
> > > and any copying, distribution or other use of any part of it
> > > is
> > > strictly prohibited. Any views or opinions presented are
> > > solely those
> > > of the sender and do not necessarily represent those of the
> > > British
> > > Geological Survey. The security of e-mail communication cannot
> > > be
> > > guaranteed and the BGS accepts no liability for claims arising
> > > as a
> > > result of the use of this medium to transmit messages from or
> > > to the
> > > BGS. . http://www.bgs.ac.uk
> > >
> > >
> ********************************************************************
> >
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
