CODEWITHSUNDEEP
oracle-databasereplace
ai03
ai03

Reputation: 81

How to use replace function in Oracle to remove a string?

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?

Upvotes: 0

Views: 234

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191275

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 $.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

We can do a regex replacement using REGEXP_REPLACE:

SELECT REGEXP_REPLACE('PAT5DSA-(ALRP)', '-\(.*?\)', '')
FROM dual;

PAT5DSA

Upvotes: 2

Related Questions