How to extract a string with several lines between brackets in oracle sql query

Question

I am trying to extract a value between the brackets from a string.

Here(How to extract a string between brackets in oracle sql query), it is explains how to do.

But in my situation, the string has 2 lines. With this way, I get only NULL.

SELECT REGEXP_SUBSTR('Gupta, Abha (01792)', '\((.+)\)', 1, 1, NULL, 1) FROM dual --01792

SELECT REGEXP_SUBSTR('Gupta, Abha (01
                 792)', '\((.+)\)', 1, 1, NULL, 1) FROM dual -- NULL

I known that i can remove the break line symbol and then use regex_substr but i need to keep the break line symbol

What you need is another option, 'n' or 'm', it depends on your data which one to use. Check Table 3-2 on docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_regexp.htm — and
– and, Commented Dec 14, 2019 at 8:54
@Daeron you should convert your comment into an answer. Working fiddle — Eldar
– Eldar, Commented Dec 14, 2019 at 9:17

GMB · Accepted Answer · 2019-12-14 14:50:56Z

2

I would adress this with the following regex:

\(([^)]*)\

This makes use of a custom character class, [^)], which means: everything but a closing parenthese. This way, you do not have to worry about line breaks (since, obviously, a line break is not a closing parenthese), or any other special character:

Demo on DB Fiddle:

SELECT REGEXP_SUBSTR('Gupta, Abha (01
                 792)', '\(([^)]*)\)') res FROM dual

| RES                  |
| :------------------- |
| (01                  |
|                 792) |

answered Dec 14, 2019 at 14:50

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

How to extract a string with several lines between brackets in oracle sql query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related