In my table, I have data like PAT5DSA-(ALRP), LAR6DAOP-(RAH) etc..But I want to remove the strings like -(xxxx) or -(xxx) which can be any alphabets inside braces. Tried using the below:
select replace(:code,'-(Aa-Zz)',null) from employee;
But this didn't work..Can anyone please help?
We can do a regex replacement using REGEXP_REPLACE:
SELECT REGEXP_REPLACE('PAT5DSA-(ALRP)', '-\(.*?\)', '')
FROM dual;
PAT5DSA
The plain replace() doesn't understand patterns. You could use a regular expression replace, e.g.:
-- CTE for sample data
with cte (code) as (
select 'PAT5DSA-(ALRP)' from dual
union all
select 'LAR6DAOP-(RAH)' from dual
)
select code, regexp_replace(code, '-\(.*?\)$') as result
from cte;
CODE RESULT
-------------- --------------
PAT5DSA-(ALRP) PAT5DSA
LAR6DAOP-(RAH) LAR6DAOP
This will remove anything inside a pair of parentheses which is preceded by a dash, at the end of the original string. If the parentheses to be removed could be anywhere in the string then remove the $.
Use INSTR and SUBSTR:
WITH cteVals AS (SELECT 'PAT5DSA-(ALRP)' AS COL_VAL FROM DUAL UNION ALL
SELECT 'LAR6DAOP-(RAH)' AS COL_VAL FROM DUAL)
SELECT SUBSTR(COL_VAL, 1, INSTR(COL_VAL, '-')-1)
FROM cteVals;
Best of luck.
REPLACEdoes not support anything as fancy.