Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: quote in string

From: aaaa wwwwww <>
Date: Wed, 27 Sep 2000 21:01:39 -0700
Message-Id: <>

 Dear Larry

Thanks a lot ! Good job !



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

>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;
>SQL> select replace(ticky,'''','''''') from ticky;
>SQL> select replace(ticky,'''','XYZ') from ticky
> 2 /
>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.
>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
>Thanks again
>-----Original Message-----
>Sent: Wednesday, September 27, 2000 4:32 PM
>To: Multiple recipients of list ORACLE-L
>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
>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
>directly in the INSTR:
>instr(name,chr(39)) > 0
>Or some people like to assign CHR(39) (or '''') to a variable and refer to
>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.
>L. Elkins
>On Wed Sep 27 14:36:39 2000, "Koivu, Lisa" <>,ORACLE-
> wrote:
>> Hi all -
>> I'm putting my developer hat on again. I am writing a procedure
>> through a string. However, the single quote ( ' ) is becoming a headache.
>> can't replace() it, I can't instr() and look for it. I am beginning to
>> 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
>> 4850 North State Road 7
>> Suite G104
>> Fort Lauderdale, FL 33319
>> V: 954.484.3191, x174
>> F: 954.484.2933
>> C: 954.658.5849
>Please see the official ORACLE-L FAQ:
>Author: larry elkins
>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: (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).
Received on Wed Sep 27 2000 - 23:01:39 CDT

Original text of this message