Oracle regex_replace not working as expected

Question

I have following SQL query (Oracle 18c):

SELECT 
    --FIRST
    translate(
        ' sOmE tEsT


                   eNdOfLiNe', 
        chr(10)||chr(11)||chr(13), 'replText'
    ) "Result1",

    --SECOND
    regexp_replace(
        ' sOmE tEsT


                   eNdOfLiNe',
        '[\x0A|\x0B|`\x0D]', 'replText'
    ) "Result2",

    --THIRD
    regexp_replace(
        ' sOmE tEsT


                   eNdOfLiNe',
        '[\r\n\t]', 'replText', 1, 0
    ) "Result3"
FROM dual

What I would like to do is replace all tabs, return carriages and new line indicators with new string but it seems like regexp replace is not working (returns initial text). I am really sorry about formatting but I need to handle text in exact format as above with \r \n \t mixed chars.

Here is fiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=63834f9bcab93136635366f18c375b13

I am learning Oracle right now and don't understand why second and third solution returns initial text. The first solution seems to work but I would like to achieve the same effect in SECOND and THIRD solution. What I missed?

If REGEXP_REPLACE() fails to match the regex to the input string, the input string is returned. Note this is in contrast to REGEXP_SUBSTR(), where if the match is not found NULL is returned. — Gary_W
– Gary_W, Commented Apr 23, 2020 at 13:08

Gary_W · Accepted Answer · 2020-04-24 13:05:32Z

1

I'm pretty sure Oracle does not allow escape sequences in a character class. I believe this is what you have to do. In response to your comment on another answer here and as you are learning, regex is most definitely not regex. Especially Oracle's implementation.

EDIT to explain the regex: The regex pattern is building a string of a regex character class containing 3 characters, hence the concatenation. You can't just have escape characters in the regex as then regex would take those characters as part of the character class pattern itself.

SELECT REGEXP_REPLACE(
' sOmE tEsT


                       eNdOfLiNe', '['||CHR(9)||CHR(10)||CHR(13)||']', 'X') Result3
FROM dual;

RESULT3                       
------------------------------
 sOmE tEsTXXXXXXXX   eNdOfLiNe
1 row selected.

edited Apr 24, 2020 at 13:05

answered Apr 23, 2020 at 14:02

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

psaraj12 · Accepted Answer · 2020-04-23 11:54:47Z

0

You can try the below using similar format as translate

  select  regexp_replace(
   ' sOmE tEsT


               eNdOfLiNe',
    chr(10)||'|'||chr(11)||'|'||chr(13), 'replText') "Result3"
    FROM dual

answered Apr 23, 2020 at 11:54

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

4 Comments

Adam Mrozek Over a year ago

Sure, but why regex doesn't work if some examples of regex are published on Oracle doc page?

psaraj12 Over a year ago

Can you provide the link to oracle docs where the newline regexp expression is mentioned

Adam Mrozek Over a year ago

There are not explicit examples of \r \n \t but regex is regex right? There are some examples in web f.e. chrismemo.wordpress.com/2018/05/18/…

Gary_W Over a year ago

@AdamMrozek That example mentioned in the link above doesn't work (at least on 11g), even after one fixes the typo (remove the backquote).

Collectives™ on Stack Overflow

Oracle regex_replace not working as expected

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related