CODEWITHSUNDEEP
oracle-databasexquery
PWL
PWL

Reputation: 23

Trying to use replace ((value),"\W","") on oracle xquery

Could someone explain why does replace \w (word-character) work and \W (non-word-character) does not . How to solve it.

create table test (xmldata) as
select xmltype('<workbook>
 <worksheet sheetName="asd-kasd" sheetId="1"/>
</workbook>') 
from dual;

update test
set XMLDATA=
           xmlquery(
             'copy $d := .
             modify (
                for $i in $d/workbook/worksheet/@sheetName
                return replace value of node $i with concat("1.\w:",replace(string($i/../@sheetName),"\w",""),"2. \W:",replace($i/../@sheetName,"\W",""))
            )
              return $d'
            passing test.XMLDATA
            returning content
         );

ORA-19112: error raised during evaluation: 
XVM-01126: [FORX0003] Regular expression matches zero-length string

fiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=ede05cbbe55f36074c36457e9491fc11

Error:

ORA-19112: error raised during evaluation: 
XVM-01126: [FORX0003] Regular expression matches zero-length string

after using \W

Upvotes: 1

Views: 481

Answers (1)

kfinity
kfinity

Reputation: 9091

This seems to be a bug in Oracle's implementation of the standard XQuery fn:replace() function. Using the metacharacter \W causes fn:replace to fail, on every string that I tested. I'd suggest opening a Service Request with Oracle Support to report it.

You can verify using an non-Oracle XQuery tester (e.g. here) that replace() should handle \W just fine.

Oddly, the deprecated ora:replace() function does work correctly. So you could use that as a workaround until Oracle patches the bug. But note that this function is non-standard - for example, it supports POSIX-style metacharacters (e.g. [[:alnum:]]) which the XQuery standard does not.

I simplified your query to give a more minimal verifiable example, and could reproduce the issue on Oracle 12.2.0.1. Comment out the "fn" column to get correct results. 

select 
    regexp_replace('asd-kasd','\W','') as rr, -- normal regexp engine works fine
    -- \W fails, with any string
    xmlquery('fn:replace("asd-kasd","\W","")' returning content) as fn,
    -- deprecated ora:replace works fine
    xmlquery('ora:replace("asd-kasd","\W","")' returning content) as ora
from dual

Fiddle

Upvotes: 1

Related Questions