CODEWITHSUNDEEP
oracle-database
cool_taps
cool_taps

Reputation: 350

Update single character in oracle

I am trying to update only one character after _

I have column value ABC123_169876357 and want to replace value after "" like ABC123**3**69876357.

I have used below function 

replace(substr('ABC123_169876357',8,1),1,3)

But it is just replacing value but I want to update value after "_"

Could anyone please help me to update?

Many Thanks in advance!

Upvotes: 0

Views: 2067

Answers (4)

Craig
Craig

Reputation: 5820

Like wolφi, I would also use REGEXP_REPLACE, but probably something more generic like:

with test_data as (
  select 'ABC123_169876357' str from dual
)
select str, regexp_replace(str,'_.','**3**') new_str
from test_data;

STR                  NEW_STR             
-------------------- --------------------
ABC123_169876357     ABC123**3**69876357

Upvotes: 2

ThinkJet
ThinkJet

Reputation: 6735

This may be done by combination of substr and instr:

with test_data as (
  select 'ABC123_169876357' str from dual
)
select 
  (
    substr(str, 1, instr(str,'_')-1)    -- get beginning of string up to '_' char
      || '**3**'                        -- concatenate with replacement string
      || substr(str, instr(str,'_')+2)  -- add rest of string
   ) as result_string   
from test_data

Upvotes: 1

ngrashia
ngrashia

Reputation: 9894

For updating the table, you can use:

UPDATE TABLE_NAME 
SET FIELD_NAME =
(SUBSTR(FIELD_NAME, 1, instr(FIELD_NAME,'_')-1) 
|| '**3**' 
|| substr(FIELD_NAME, instr(FIELD_NAME,'_')+2))

WHERE FIELD_NAME = 'ABC123_169876357'

Replace TABLE_NAME with your table name and FIELD_NAME with corresponding field name.

Upvotes: 1

wolφi
wolφi

Reputation: 8361

I'd use REGEXP_REPLACE for that:

SELECT regexp_replace('ABC123_169876357', '(ABC123).*(69876357)','\1xxx\2') FROM DUAL;

ABC123xxx69876357

would that solve your question?

Upvotes: 1

Related Questions