Home » SQL & PL/SQL » SQL & PL/SQL » single quots problem
single quots problem [message #248301] Thu, 28 June 2007 10:26 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi,

i am trying to replace ' to '' but i am not able ..plz suggest me how much ' require in search string and replace sting arguments in replace function.

declare
v_search_query varchar2(40) := '(''EQUIPMENT'')';
v_result varchar2(50);
begin

dbms_output.put_line(v_search_query);

SELECT REPLACE(v_search_query, ''', ''''') into v_result
FROM dual;

dbms_output.put_line(v_result);
			
end;



--Yash



Re: single quots problem [message #248304 is a reply to message #248301] Thu, 28 June 2007 10:40 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
You need one more single quote in each section of your REPLACE function...

SELECT REPLACE(v_search_query, '''', '''''') into v_result
FROM dual;



It's easier to visualize if you put a character in. For example, you want to change a single quote ('x''x') into two single quotes ('x''x''x'). This example would change x'x to x''x. Now, remove the x's and you're set.

HTH,
Ron
Re: single quots problem [message #248305 is a reply to message #248301] Thu, 28 June 2007 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No need of SELECT:
SQL> declare
  2  v_search_query varchar2(40) := '(''EQUIPMENT'')';
  3  v_result varchar2(50);
  4  begin
  5  
  6  dbms_output.put_line(v_search_query);
  7  
  8  v_result := REPLACE(v_search_query, '''', '''''');
  9  dbms_output.put_line(v_result);
 10     
 11  end;
 12  /
('EQUIPMENT')
(''EQUIPMENT'')

PL/SQL procedure successfully completed.

Regards
Michel

[edit: change " to '']

[Updated on: Thu, 28 June 2007 10:43]

Report message to a moderator

Re: single quots problem [message #248310 is a reply to message #248305] Thu, 28 June 2007 10:48 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Michel!! it is working fine ....
Previous Topic: GROUP BY is showing error
Next Topic: Function is not working in distributed transaction (with dblink)
Goto Forum:
  


Current Time: Mon Dec 05 13:04:39 CST 2016

Total time taken to generate the page: 0.11223 seconds