Home » SQL & PL/SQL » SQL & PL/SQL » error while removing ' using regex
error while removing ' using regex [message #669911] Thu, 24 May 2018 02:13 Go to next message
purnima1
Messages: 76
Registered: June 2014
Member
Hi Team please confirm how can we remove special charcter ' when passed in variable
create table test (name1 varchar2(1000));
insert into  test values ('purnima''bhatia');




create or replace function remove_spl_char (p_input_value varchar2,p_replace_pattern varchar2,P_REPLACE_WITH VARCHAR2) return number as
v_final_result varchar2(32767);
v_stmt varchar2(32767);
begin 
v_stmt:='SELECT REGEXP_REPLACE( '''|| P_INPUT_VALUE ||''', '''||P_REPLACE_PATTERN||''','''||P_REPLACE_WITH||''') FROM DUAL';
 dbms_output.put_line(v_stmt);
 EXECUTE IMMEDIATE 'SELECT REGEXP_REPLACE( '''|| P_INPUT_VALUE ||''', '''||P_REPLACE_PATTERN||''','''||P_REPLACE_WITH||''') FROM DUAL' INTO  v_final_result ;
 dbms_output.put_line(v_final_result);
 return 0;
 
 end ;
 
declare 
v number ;
p_input_value varchar2(32767);
begin 
select name1 into p_input_value from test ; 
v:= remove_spl_char(p_input_value,'chr(39)','');
end ;
----error 
--ORA-00907: missing right parenthesis
--ORA-06512: at "XXGMDMADM.REMOVE_SPL_CHAR", line 4
--ORA-06512: at line 6
 
 select REGEXP_REPLACE('purnima''bhatia',chr(39),'') from dual 
 ---output purnimabhatia


we are getting error because below mentioned statement is getting prepared.
SELECT REGEXP_REPLACE( 'purnima'bhatia', 'chr(39)','') FROM DUAL;


Kindly help how we can correct this

[Updated on: Thu, 24 May 2018 02:15]

Report message to a moderator

Re: error while removing ' using regex [message #669912 is a reply to message #669911] Thu, 24 May 2018 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Still no feedback in your previous topics of these last 2 days.
Still not following the rules.

Michel Cadot wrote on Tue, 22 May 2018 12:53

Why don't feedback and follow your previous topic before starting a new one?
...
Point 4 of OraFAQ Forum Guide:

Quote:
Post your Oracle DB version to 4 decimal places (SELECT * FROM V$VERSION;).
Re: error while removing ' using regex [message #669919 is a reply to message #669911] Thu, 24 May 2018 05:44 Go to previous messageGo to next message
purnima1
Messages: 76
Registered: June 2014
Member
Hi All this is resolved .I was doing it wrong. Ideally I can directly used regex with incoming parameters .
Re: error while removing ' using regex [message #669920 is a reply to message #669919] Thu, 24 May 2018 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 24 May 2018 12:38

OraFAQ Forum Guide, point 12:

Quote:
If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.
Still not following the rules.

Re: error while removing ' using regex [message #670041 is a reply to message #669920] Wed, 30 May 2018 12:33 Go to previous message
Bill B
Messages: 1927
Registered: December 2004
Senior Member
why do you need regular expressions to remove a special character. The replace command runs MUCH faster and is easier to use

SELECT REPLACE( my_column, chr(39)) FROM DUAL;

This will replace all occurrences of chr(39) with a null
Previous Topic: wants to generate XML
Next Topic: cannot use UTL_SMTP on Windows 10 pro from machine at home
Goto Forum:
  


Current Time: Wed Jan 29 01:39:40 CST 2020