How to remove only opening and closing bracket from a string in oracle?

user_10104358 0
Viewed 192

I have a string like this:

(1/8in Cap TBE ASME B1.20.1 (NPT) Male x Fem CL400 CU Alloy B152 Gr C10400 Cement Lined)

I need output like this:

1/8in Cap TBE ASME B1.20.1 (NPT) Male x Fem CL400 CU Alloy B152 Gr C10400 Cement Lined

i.e. only remove the brackets at the start and end not in the middle.

Also if the string is like this, either having starting bracket or closing bracket

(1/8in Cap TBE ASME B1.20.1 (NPT) Male x Fem CL400 CU Alloy B152 Gr C10400 Cement Lined

or

1/8in Cap TBE ASME B1.20.1 (NPT) Male x Fem CL400 CU Alloy B152 Gr C10400 Cement Lined)

then don't remove the brackets.

I tried using regexp_replace but couldn't find the solution. Any help will be appreciable

oracle
1 Answers

As simple CASE is enough to remove the leading and ending brackets

with txt as (
select '(xxxx)' txt from dual union all
select '(x(x)xx)' txt from dual union all
select '(xxxx' txt from dual union all
select 'xxxx)' txt from dual union all
select 'xxxx' txt from dual)
select txt,
case when txt like '(%)' then 
    substr(txt,2,length(txt)-2)
else
   txt
end as new_txt
from txt

gives

TXT      NEW_TXT
-------- --------
(xxxx)   xxxx     
(x(x)xx) x(x)xx   
(xxxx    (xxxx    
xxxx)    xxxx)    
xxxx     xxxx

Alternatively you may use REGEXP as follows

select 
  txt,
  nvl(
     REGEXP_SUBSTR(txt, '^\((.*)\)$', 1, 1, null, 1),
     txt)
  as txt_new
from txt

The regexp '^\((.*)\)$' enforced the leading and trailing bracket and returns the part between - first group (the fifth parameter of REGEXP_SUBSTR).

In case the REGEXP_SUBSTR return noting, i.e. there expression is not enclosed in brackets the NVL return the original value.

user_48081220
Related