You did not have enough quotes in your string, use the following:
str1 := replace(v_str, ',', ''',''');
To put a single quote in a string, the rules are:
1) in the middle of a string use two quotes: ''
2) at the beginning or end of a string use three quotes (one for the string quote and then two to signify the inserted quote: '''
3) a stand along quote in a string use 4 quotes (one for the string quote and then two to signify the inserted quote and one for the end string quote): ''''
michael smith
-----Original Message-----
From: Chamberlain John
[mailto:oracledba-ezmlmshield-x48999735.[Email address protected]
Sent: Tuesday, May 30, 2006 2:32 AM
To: LazyDBA Discussion
Subject: RE: Problem replace function
Why not use the chr function for the quote and concatenate it into the
result? You can include the quotes at each end as well...
Use the form:
Str1:=chr(39)||replace(v_str,',',chr(39)||','||chr(39))||chr(39);
Chr gets round all sorts of issues with quote characters!!
HTH
John.
-----Original Message-----
From: Vinherkar Aashish C (MLITS)
[mailto:oracledba-ezmlmshield-x20123246.[Email address protected]
Sent: 29 May 2006 14:17
To: LazyDBA Discussion
Subject: Problem replace function
Hi all,
I have a string with comma separated need to convert it into single
quoted string.
For example v_str:=A,B,C
Need to convert into v_str1:='A','B','C'
Tried to use the replace function to replace , with ','
And then append the former and latter part of string with single quotes
to get the required string 'A','B','C'.
The replace function used is as follows;-
str1:= replace(v_str,',','','');
The replace function does not do it .
How to use the replace function for this type of conversion
I feel it is a problem because the single quotes need to be escaped by
using escape character
Doing such conversion to pass to IN Clause.
Thanks in advance.
Regards,
Aashish
--------------------------------------------------------
If you are not an intended recipient of this e-mail, please notify the
sender, delete it and do not read, act upon, print, disclose, copy, retain
or redistribute it. Click here for important additional terms relating to
this e-mail. http://www.ml.com/email_terms/
--------------------------------------------------------
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
-----------------------------------------
Information in this email including any attachments may be
privileged, confidential and is intended exclusively for the
addressee. The views expressed may not be official policy, but the
personal views of the originator. If you have received it in error,
please notify the sender by return e-mail and delete it from your
system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone.
Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.
SKY and the SKY marks are trade marks of British Sky Broadcasting
Group plc and are used under licence. British Sky Broadcasting
Limited (Registration No. 2906991), Sky Interactive Limited
(Registration No. 3554332), Sky-In-Home Service Limited
(Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of
British Sky Broadcasting Group plc (Registration No. 2247735). All
of the companies mentioned in this paragraph are incorporated in
England and Wales and share the same registered office at Grant
Way, Isleworth, Middlesex TW7 5QD.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page