| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: quote in string
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
SQL> select replace(ticky,'''','''''') from ticky;
REPLACE(TICKY,'''','
SQL> select replace(ticky,'''','XYZ') from ticky 2 /
REPLACE(TICKY,'''','XYZ')
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> 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> 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> select replace(ticky,'''','XYZ') from ticky</FONT> <BR><FONT SIZE=2> 2 /</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&3 and 4&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,'''') > 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; 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)) > 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, "Koivu, Lisa" <lkoivu_at_qode.com>,ORACLE-</FONT> <BR><FONT SIZE=2>L_at_fatcity.com wrote:</FONT> <BR><FONT SIZE=2>> Hi all -</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>> I'm putting my developer hat on again. I am writing a procedure</FONT> <BR><FONT SIZE=2>that</FONT> <BR><FONT SIZE=2>parses</FONT> <BR><FONT SIZE=2>> through a string. However, the single quote ( ' ) is becoming a headache.</FONT> <BR><FONT SIZE=2>I</FONT> <BR><FONT SIZE=2>> 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>> the only way I can get around this when I trip onto it in my code (and get</FONT> <BR><FONT SIZE=2>> it out of my string) is to fall into an exception and check character by</FONT> <BR><FONT SIZE=2>> character and recreate the string in a variable, and handle the exception</FONT> <BR><FONT SIZE=2>> caused by the single quote when it occurs, ignoring the current character,</FONT> <BR><FONT SIZE=2>> and continuing.</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>> Has anyone got a better idea?</FONT> <BR><FONT SIZE=2>> Thanks in advance for any suggestions.</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>> Lisa Rutland Koivu</FONT> <BR><FONT SIZE=2>> Oracle Database Administrator</FONT> <BR><FONT SIZE=2>> Qode.com</FONT> <BR><FONT SIZE=2>> 4850 North State Road 7</FONT> <BR><FONT SIZE=2>> Suite G104</FONT> <BR><FONT SIZE=2>> Fort Lauderdale, FL 33319</FONT> <BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>> V: 954.484.3191, x174</FONT> <BR><FONT SIZE=2>> F: 954.484.2933</FONT> <BR><FONT SIZE=2>> C: 954.658.5849</FONT> <BR><FONT SIZE=2>> http://www.qode.com</FONT> <BR><FONT SIZE=2>></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> INET: elkinsl_at_flash.net</FONT> </P> <P><FONT SIZE=2>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=2>San Diego, California -- 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
 |
 |