CODEWITHSUNDEEP
sqloracle-database
Abha
Abha

Reputation: 347

How to extract a string between brackets in oracle sql query

I am trying to extract a value between the brackets from a string. How can I do that.

For eg: I have this string : Gupta, Abha (01792)

And I want to get the result between the brackets i.e : 01792

I am trying to write a query like this :

select substr('Gupta, Abha (01792)',instr('Gupta, Abha (01792)','(')+1,5) from dual;

which actually gives me the result. But the problem is that the number of characters between the brackets is not necessariliy always 5.

So I want to avoid hardcoding '5' in the query.

Can somebody please let me know how can I make this query more generic.

Thanks, Abha

Upvotes: 1

Views: 9453

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

We can try using REGEXP_SUBSTR here:

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

Upvotes: 5

Related Questions