This discussion is archived
4 Replies Latest reply: Dec 11, 2012 5:44 AM by BluShadow RSS

Using regexp_replace to replace control chars and ;

tinku981 Newbie
Currently Being Moderated
Hello,

I wanted to replace control characters by SPACE and semi colon (;) by comma (,) that are in same column. Could you please help me same?

create table t (char_value varchar2(30));
insert into t (char_value) values ('a' || chr(9) || 'b' || chr(10) || 'c' || chr(13) || 'd' || ';');
select * from t;

Following is desired output which can be achieved by translate
a b c d,
select translate(char_value,chr(9)||chr(10)||chr(13)||';',' ,') from t;

But can we do same by using regular expression. I am able to just replace the control character but not able to use same for replace ;
select regexp_replace(char_value, '[[:cntrl:]]', ' ') from t;

Thanks in advance,
  • 1. Re: Using regexp_replace to replace control chars and ;
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Sandeep98191 wrote:
    ... Following is desired output which can be achieved by translate
    a b c d,
    select translate(char_value,chr(9)||chr(10)||chr(13)||';',' ,') from t;

    But can we do same by using regular expression. I am able to just replace the control character but not able to use same for replace ;
    No, I don't think you can do this with just one REGEXP_REPLACE (or any regular expression function).
    It looks like TRANSLATE is the best tool for this job.
  • 2. Re: Using regexp_replace to replace control chars and ;
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Sandeep,


    I think TRANSLATE is a better and more performant option. Why you want to go to regular expression?

    If you want to do it with regular expression the best would be to nest to REGEXP_REPLACE. In one single REGEXP_REPLACE function is going to be tricky.
    Why you want to go for that solution when you already have one working?

    Regards.
    Al
  • 3. Re: Using regexp_replace to replace control chars and ;
    tinku981 Newbie
    Currently Being Moderated
    I am wondering if translate can be replaced with regular expression. But using nested regular expression may have preformance impact. Therefore, I stick to translate in this case.

    Thanks for your quick reply.
  • 4. Re: Using regexp_replace to replace control chars and ;
    BluShadow Guru Moderator
    Currently Being Moderated
    Sandeep98191 wrote:
    Hello,

    I wanted to replace control characters by SPACE and semi colon (;) by comma (,) that are in same column. Could you please help me same?

    create table t (char_value varchar2(30));
    insert into t (char_value) values ('a' || chr(9) || 'b' || chr(10) || 'c' || chr(13) || 'd' || ';');
    select * from t;

    Following is desired output which can be achieved by translate
    a b c d,
    select translate(char_value,chr(9)||chr(10)||chr(13)||';',' ,') from t;

    But can we do same by using regular expression. I am able to just replace the control character but not able to use same for replace ;
    select regexp_replace(char_value, '[[:cntrl:]]', ' ') from t;
    You can't do that with regular expressions because you can't say "replace this with that AND this other with that other" all as one expression. Of course you can do two seperate replaces:
    SQL> select replace(regexp_replace(char_value,'[[:cntrl:]]',' '),';',',') from t;
    
    REPLACE(REGEXP_REPLACE(CHAR_VALUE,'[[:CNTRL:]]',''),';',',')
    ---------------------------------------------------------------------------------
    a b c d,
    but as others have said, a translate may be more appropriate in this case.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points