Replace a part of a varchar2 column in Oracle

Question

I've a varchar2 column in a table which contains a few entries like the following TEMPORARY-2 TIME ECS BOUND -04-Insuficient Balance I want to update these entries and make it TEMPORARY-2 X. What's the way out?

Answer 1

To accomplish this, you can either use character functions such as substr(), replace()

or a regular expression function - regexp_replace() for instance.

SQL> with t1(col) as(
  2    select 'TEMPORARY-2 TIME ECS BOUND -04-Insuficient Balance'
  3      from dual
  4  )
  5  select concat(substr( col, 1, 11), ' X')             as res_1
  6       , regexp_replace(col, '^(\w+-\d+)(.*)', '\1 X') as res_2
  7  from t1
  8  ;

Result:

RES_1         RES_2
------------- -------------
TEMPORARY-2 X TEMPORARY-2 X

So your update statement may look like this:

update your_table t
   set t.col_name = regexp_replace(col_name, '^(\w+-\d+)(.*)', '\1 X')
 -- where clause if needed.