CODEWITHSUNDEEP
regexoracle-databaseplsql
Shikha
Shikha

Reputation: 93

Extract data outside of parentheses in oracle

I have this value: (203)1669

My requirement is to extract data which is outside of the parentheses.

I want to use Regular expression for this Oracle query.

Much appreciated!

Upvotes: 2

Views: 1508

Answers (4)

Gary_W
Gary_W

Reputation: 10360

This example uses REGEXP_SUBSTR() and the REGEX explicitly follows your spec of getting the 4 digits between the closing paren and the end of the line. If there could be a different number of digits, replace the {4} with a + for one or more digits:

SQL> with tbl(str) as (
      select '(203)1669' from dual
    )
    select regexp_substr(str, '\)(\d{4})$', 1, 1, NULL, 1) nbr
    from tbl;

NBR
----
1669

SQL>

Upvotes: 0

Utsav
Utsav

Reputation: 8093

For the pattern you mentioned, this should work.

select  
rtrim(ltrim(substr(phone_number,instr(phone_number,')')+1,length(phone_number)))) 
as derived_phone_no
from 
(select '(123)456' as phone_number from dual union all
 select '(567)99084' as phone_number from dual)

Here first I am getting position of ) and then getting substr from the position of ) + 1 till the length of the string. As a best practice, you can use trim functions.

Upvotes: -1

Md. Shamim Al Mamun
Md. Shamim Al Mamun

Reputation: 386

You can use the combination of SUBSTR and INSTR function.

select substr('(203)1669', instr('(203)1669',')')+1) from dual

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

You can use the Oracle REGEXP_REPLACE() function, and match the group which is outside the parentheses.

SELECT REGEXP_REPLACE(phone_number, '\([[:digit:]]+\)(.*)', '\1') AS newValue
FROM your_table

Upvotes: 4

Related Questions