Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: quote in string

RE: quote in string

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Thu, 28 Sep 2000 08:30:10 -0400
Message-Id: <10633.118134@fatcity.com>


This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C02947.D7ADF976
Content-Type: text/plain;

        charset="iso-8859-1"

Larry, again you have been so helpful. Very clever! Thanks for your response.
Lisa

-----Original Message-----
From: larry elkins [mailto:elkinsl_at_flash.net] Sent: Wednesday, September 27, 2000 8:26 PM To: Multiple recipients of list ORACLE-L Subject: RE: quote in string

Lisa,

You already have a workaround; but, here is how you can handle it in a REPLACE. You don't mention what you want to replace it with; but, the same concept applies:

SQL> select * from ticky;

TICKY



O'Connor

SQL> select replace(ticky,'''','''''') from ticky;

REPLACE(TICKY,'''','



O''Connor

SQL> select replace(ticky,'''','XYZ') from ticky   2 /

REPLACE(TICKY,'''','XYZ')



OXYZConnor

I threw the first example in there just in case you are dealing with dynamic code of some sort where you need to replace a single quote with a double quote. I'm doing a lot of DBMS_SQL in various packages and use a generic function in the DB for replacing single quotes with double quotes for subsequent DML and SELECT statements. Quotes 1 and 6 delimit the string, each pair of quotes in the middle, 2&3 and 4&5, each spit out a single quote, effectively replacing a single quote with a double quote.

The second example shows how to use the four quotes in a REPLACE and substitute whatever you like.

You already have things worked out using a different method; but, I thought you might still find the above helpful.

Regards,

Larry Elkins

-----Original Message-----
Sent: Wednesday, September 27, 2000 4:22 PM To: Multiple recipients of list ORACLE-L

Thanks for your suggestions.
However the root of the problem is the replace(). I can get around it with what you have suggested though by substr() and concatenating it back together.
Thanks again
Lisa

-----Original Message-----
Sent: Wednesday, September 27, 2000 4:32 PM To: Multiple recipients of list ORACLE-L

Lisa,
Try coding the INSTR as:
instr(name,'''') > 0
The first quote denotes the start of the string, the second and third quotes gives one quote, and the fourth closes the string. Try the following 2 queries
to get a feel for it:
select 'O''Connor' From Dual; 2 quotes outputting one quote select '''' from dual; Now outputting just a single quote If seeing so many tickies starts to get confusing, you can use the CHR function
directly in the INSTR:
instr(name,chr(39)) > 0
Or some people like to assign CHR(39) (or '''') to a variable and refer to the
variable throughout their code. Those are just a few of the *many* ways to handle this. You may prefer other suggestions that are sure to appear. Regards,
L. Elkins
On Wed Sep 27 14:36:39 2000, "Koivu, Lisa" <lkoivu_at_qode.com>,ORACLE- L_at_fatcity.com wrote:
> Hi all -
>
> I'm putting my developer hat on again. I am writing a procedure
that
parses
> through a string. However, the single quote ( ' ) is becoming a headache.
I
> can't replace() it, I can't instr() and look for it. I am beginning to
think
> the only way I can get around this when I trip onto it in my code (and get
> it out of my string) is to fall into an exception and check character by
> character and recreate the string in a variable, and handle the exception
> caused by the single quote when it occurs, ignoring the current character,
> and continuing.
>
> Has anyone got a better idea?
> Thanks in advance for any suggestions.
>
> Lisa Rutland Koivu
> Oracle Database Administrator
> Qode.com
> 4850 North State Road 7
> Suite G104
> Fort Lauderdale, FL 33319
>
> V: 954.484.3191, x174
> F: 954.484.2933
> C: 954.658.5849
> http://www.qode.com
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: larry elkins
  INET: elkinsl_at_flash.net

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).

------_=_NextPart_001_01C02947.D7ADF976
Content-Type: text/html;
	charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2650.12">
<TITLE>RE: quote in string</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=2>Larry, again you have been so helpful. Very clever!</FONT>
<BR><FONT SIZE=2>Thanks for your response. </FONT>
<BR><FONT SIZE=2>Lisa</FONT>
</P>

<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: larry elkins [<A HREF="mailto:elkinsl_at_flash.net">mailto:elkinsl_at_flash.net</A>]</FONT>
<BR><FONT SIZE=2>Sent: Wednesday, September 27, 2000 8:26 PM</FONT>
<BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=2>Subject: RE: quote in string</FONT>
</P>
<BR>

<P><FONT SIZE=2>Lisa,</FONT>
</P>

<P><FONT SIZE=2>You already have a workaround; but, here is how you can handle it in a</FONT>
<BR><FONT SIZE=2>REPLACE. You don't mention what you want to replace it with; but, the same</FONT>
<BR><FONT SIZE=2>concept applies:</FONT>
</P>

<P><FONT SIZE=2>SQL&gt; select * from ticky;</FONT>
</P>

<P><FONT SIZE=2>TICKY</FONT>
<BR><FONT SIZE=2>----------</FONT>
<BR><FONT SIZE=2>O'Connor</FONT>
</P>

<P><FONT SIZE=2>SQL&gt; select replace(ticky,'''','''''') from ticky;</FONT>
</P>

<P><FONT SIZE=2>REPLACE(TICKY,'''','</FONT>
<BR><FONT SIZE=2>--------------------</FONT>
<BR><FONT SIZE=2>O''Connor</FONT>
</P>

<P><FONT SIZE=2>SQL&gt; select replace(ticky,'''','XYZ') from ticky</FONT>
<BR><FONT SIZE=2>&nbsp; 2&nbsp; /</FONT>
</P>

<P><FONT SIZE=2>REPLACE(TICKY,'''','XYZ')</FONT>
<BR><FONT SIZE=2>------------------------------</FONT>
<BR><FONT SIZE=2>OXYZConnor</FONT>
</P>

<P><FONT SIZE=2>I threw the first example in there just in case you are dealing with dynamic</FONT>
<BR><FONT SIZE=2>code of some sort where you need to replace a single quote with a double</FONT>
<BR><FONT SIZE=2>quote. I'm doing a lot of DBMS_SQL in various packages and use a generic</FONT>
<BR><FONT SIZE=2>function in the DB for replacing single quotes with double quotes for</FONT>
<BR><FONT SIZE=2>subsequent DML and SELECT statements. Quotes 1 and 6 delimit the string,</FONT>
<BR><FONT SIZE=2>each pair of quotes in the middle, 2&amp;3 and 4&amp;5, each spit out a single</FONT>
<BR><FONT SIZE=2>quote, effectively replacing a single quote with a double quote.</FONT>
</P>

<P><FONT SIZE=2>The second example shows how to use the four quotes in a REPLACE and</FONT>
<BR><FONT SIZE=2>substitute whatever you like.</FONT>
</P>

<P><FONT SIZE=2>You already have things worked out using a different method; but, I thought</FONT>
<BR><FONT SIZE=2>you might still find the above helpful.</FONT>
</P>

<P><FONT SIZE=2>Regards,</FONT>
</P>
<BR>

<P><FONT SIZE=2>Larry Elkins</FONT>
</P>

<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>Sent: Wednesday, September 27, 2000 4:22 PM</FONT>
<BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT>
</P>
<BR>

<P><FONT SIZE=2>Thanks for your suggestions.</FONT>
<BR><FONT SIZE=2>However the root of the problem is the replace(). I can get around it with</FONT>
<BR><FONT SIZE=2>what you have suggested though by substr() and concatenating it back</FONT>
<BR><FONT SIZE=2>together.</FONT>
<BR><FONT SIZE=2>Thanks again</FONT>
<BR><FONT SIZE=2>Lisa</FONT>
</P>
<BR>

<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>Sent: Wednesday, September 27, 2000 4:32 PM</FONT>
<BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT>
</P>
<BR>

<P><FONT SIZE=2>Lisa,</FONT>
<BR><FONT SIZE=2>Try coding the INSTR as:</FONT>
<BR><FONT SIZE=2>instr(name,'''') &gt; 0</FONT>
<BR><FONT SIZE=2>The first quote denotes the start of the string, the second and third quotes</FONT>
<BR><FONT SIZE=2>gives one quote, and the fourth closes the string. Try the following 2</FONT>
<BR><FONT SIZE=2>queries</FONT>
<BR><FONT SIZE=2>to get a feel for it:</FONT>
<BR><FONT SIZE=2>select 'O''Connor' From Dual; 2 quotes outputting one quote</FONT>
<BR><FONT SIZE=2>select '''' from dual;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Now outputting just a single quote</FONT>
<BR><FONT SIZE=2>If seeing so many tickies starts to get confusing, you can use the CHR</FONT>
<BR><FONT SIZE=2>function</FONT>
<BR><FONT SIZE=2>directly in the INSTR:</FONT>
<BR><FONT SIZE=2>instr(name,chr(39)) &gt; 0</FONT>
<BR><FONT SIZE=2>Or some people like to assign CHR(39) (or '''') to a variable and refer to</FONT>
<BR><FONT SIZE=2>the</FONT>
<BR><FONT SIZE=2>variable throughout their code. Those are just a few of the *many* ways to</FONT>
<BR><FONT SIZE=2>handle this. You may prefer other suggestions that are sure to appear.</FONT>
<BR><FONT SIZE=2>Regards,</FONT>
<BR><FONT SIZE=2>L. Elkins</FONT>
<BR><FONT SIZE=2>On Wed Sep 27 14:36:39 2000, &quot;Koivu, Lisa&quot; &lt;lkoivu_at_qode.com&gt;,ORACLE-</FONT>
<BR><FONT SIZE=2>L_at_fatcity.com wrote:</FONT>
<BR><FONT SIZE=2>&gt; Hi all -</FONT>
<BR><FONT SIZE=2>&gt;</FONT>
<BR><FONT SIZE=2>&gt; I'm putting my developer hat on again.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I am writing a procedure</FONT>
<BR><FONT SIZE=2>that</FONT>
<BR><FONT SIZE=2>parses</FONT>
<BR><FONT SIZE=2>&gt; through a string.&nbsp; However, the single quote ( ' ) is becoming a headache.</FONT>
<BR><FONT SIZE=2>I</FONT>
<BR><FONT SIZE=2>&gt; can't replace() it, I can't instr() and look for it. I am beginning to</FONT>
<BR><FONT SIZE=2>think</FONT>
<BR><FONT SIZE=2>&gt; the only way I can get around this when I trip onto it in my code (and get</FONT>
<BR><FONT SIZE=2>&gt; it out of my string) is to fall into an exception and check character by</FONT>
<BR><FONT SIZE=2>&gt; character and recreate the string in a variable, and handle the exception</FONT>
<BR><FONT SIZE=2>&gt; caused by the single quote when it occurs, ignoring the current character,</FONT>
<BR><FONT SIZE=2>&gt; and continuing.</FONT>
<BR><FONT SIZE=2>&gt;</FONT>
<BR><FONT SIZE=2>&gt; Has anyone got a better idea?</FONT>
<BR><FONT SIZE=2>&gt; Thanks in advance for any suggestions.</FONT>
<BR><FONT SIZE=2>&gt;</FONT>
<BR><FONT SIZE=2>&gt; Lisa Rutland Koivu</FONT>
<BR><FONT SIZE=2>&gt; Oracle Database Administrator</FONT>
<BR><FONT SIZE=2>&gt; Qode.com</FONT>
<BR><FONT SIZE=2>&gt; 4850 North State Road 7</FONT>
<BR><FONT SIZE=2>&gt; Suite G104</FONT>
<BR><FONT SIZE=2>&gt; Fort Lauderdale, FL&nbsp; 33319</FONT>
<BR><FONT SIZE=2>&gt;</FONT>
<BR><FONT SIZE=2>&gt; V: 954.484.3191, x174</FONT>
<BR><FONT SIZE=2>&gt; F: 954.484.2933</FONT>
<BR><FONT SIZE=2>&gt; C: 954.658.5849</FONT>
<BR><FONT SIZE=2>&gt; http://www.qode.com</FONT>
<BR><FONT SIZE=2>&gt;</FONT>
</P>

<P><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Please see the official ORACLE-L FAQ: http://www.orafaq.com</FONT>
<BR><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Author: larry elkins</FONT>
<BR><FONT SIZE=2>&nbsp; INET: elkinsl_at_flash.net</FONT>
</P>

<P><FONT SIZE=2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=2>San Diego, California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet access / Mailing Lists</FONT>
<BR><FONT SIZE=2>--------------------------------------------------------------------</FONT>
<BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail message</FONT>
<BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT>
Received on Thu Sep 28 2000 - 07:30:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US