error while removing ' using regex [message #669911] |
Thu, 24 May 2018 02:13 |
|
purnima1
Messages: 79 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 #670041 is a reply to message #669920] |
Wed, 30 May 2018 12:33 |
Bill B
Messages: 1971 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
|
|
|