Oracle SQL Regular Expression RegExp_SubStr End Of Line (chr(10) in search text returns null

user_9332190 0
Viewed 535

I am trying to get a specific part of a text with Oracle regular expression. If there is no end of line character (chr(10)) in the text, I get what I want. But if there is an end of line character, it returns null. You can see the sample sql codes below.

SELECT RegExp_SubStr('TEXT_BEGIN Line 1 Line 2 TEXT_END',  
                     'TEXT_BEGIN(.+)TEXT_END', 1, 1, NULL, 1) 
  FROM dual;

returns Line 1 Line 2

with end of line char.

SELECT RegExp_SubStr('TEXT_BEGIN Line 1' || chr(10) || 'Line 2 TEXT_END', 
                     'TEXT_BEGIN(.+)TEXT_END', 1, 1, NULL, 1) 
    FROM dual;

returns NULL

It may be a solution to convert the end of line characters in the text to a special character such as ## CHR10 ## before SubStr and then back after SubStr. But I want a simple solution without hack.

oracle sql regex
1 Answers

By default, the dot does not match the newline character.

The fifth argument to regexp_substr (which you have as NULL in your example) is used for a few modifiers. One of them is 'n' to allow the dot to match newline.

So - it's really easy: change the fifth argument from NULL to 'n'.

Admin1
Related