| 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
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-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Koivu, Lisa
Sent: Wednesday, September 27, 2000 4:22 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: quote in string
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-----
From: elkinsl_at_flash.net [mailto:elkinsl_at_flash.net]
Sent: Wednesday, September 27, 2000 4:32 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: quote in string
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
Received on Wed Sep 27 2000 - 18:28:08 CDT
 |
 |