CODEWITHSUNDEEP
sqlregexoracle11gboundary
kpater87
kpater87

Reputation: 1270

\b regular expression character in Oracle 11g

Unfortunately \b regular expression character doesn't work in Oracle.

As a workaround I found following expression: 

(^|\s|\W)(100100|100101|100102|100103)($|\s|\W)

(see: The missing \b regular expression special character in Oracle.), but in the test string data:

Test string 100100/100101, ABC-DEF,  100102 100103 test data abc100100 100100abc.

values 100101 and 100103 are not matched, while I am expecting them to be matched like it is the case of \b expression.

Is there any way to make it working? I am using Oracle 11g.

I would be appreciated for any help.

EDIT:

My goal is to tag all matches. The output that I am expecting is:

Test string [ddd]100100[/ddd]/[ddd]100101[/ddd], ABC-DEF,  [ddd]100102[/ddd] [ddd]100103[/ddd] test data abc100100 100100abc.

In this purpose I am using following statement:

regexp_replace(p_text,'(^|\s|\W)(' || l_ids || ')($|\s|\W)', '\1[ddd]\2[/ddd]\3');

Where:

EDIT 2:

In the above test string value 100100 should not be matched in the word abc100100 as well as 100100abc.

Upvotes: 1

Views: 696

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

Assuming -

  • chr(1) does not appear in the text
  • Any character that is not in [a-zA-Z0-9] is considered as a delimiter (e.g. /)
with t (p_text) as (select 'Test string 100100/100101, ABC-DEF,  100102 100103 test data abc100100 100100abc.' from dual)

select  replace
        (
            regexp_replace
            (
                regexp_replace
                (
                    p_text
                   ,'([a-zA-Z0-9]+)'
                   ,chr(1) || '\1' || chr(1)
                )
               ,chr(1) || '(100100|100101|100102|100103)' || chr(1)
               ,'[ddd]\1[/ddd]'
            )
           ,chr(1)
        ) 

from    t

Test string [ddd]100100[/ddd]/[ddd]100101[/ddd], ABC-DEF, [ddd]100102[/ddd] [ddd]100103[/ddd] test data abc100100 100100abc.

Upvotes: 0

Related Questions