RE: quote in string

 Dear Larry

Thanks a lot ! Good job !

Rao

--

On Wed, 27 Sep 2000 16:25:30  
 larry elkins wrote:


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