Replacing a specific string in oracle I've a database value 'INS. Company Cancelled' where multiple values are separated by '.'
I'm trying to replace 'INS' with 'INSEXP' my expected result is 'INSEXP. Company Cancelled'
I've tried below two queries to update the field my output is like 'INSEXP. Company Cancelled. Company Cancelled'
update my_table
SET column_name = (select replace(column_name, 'INS', 'INSEXP') 
                    from my_table WHERE seq_num = 123)
WHERE seq_num = 123;

update my_table
set column_name = replace(column_name, 'INS', 'INSEXP')
WHERE seq_num = 123;

Could someone please tell me what I'm doing wrong here?
You want to do something like this (see fiddle here):
CREATE TABLE test 
(
  t_id INTEGER PRIMARY KEY,
  x VARCHAR (50),
  seq_num INTEGER NOT NULL
);


INSERT INTO test VALUES (1, 'INS. Company Cancelled - 127', 127);
INSERT INTO test VALUES (2, 'INS. Company Cancelled - 128', 128);
INSERT INTO test VALUES (3, 'INS. Company Cancelled - 126_1', 126);
INSERT INTO test VALUES (4, 'INS. Company Cancelled - 126_2', 126);


The record below has 126 as the seq_num and the fragment INS.  isn't at the beginning of the target string:
-- Note: 126 as seq_num!
--
-- BUT, also note that the `INS.` isn't at the beginning of the string!
--

INSERT INTO test 
VALUES 
(5, 'stuff... ___INS. Company Cancelled - 126', 126); 

Then:
SELECT REPLACE(x, 'INS. ', 'INSEXP. ') AS &quot;Check:&quot; FROM test;

Result:
Check:
INSEXP. Company Cancelled - 127
INSEXP. Company Cancelled - 128
INSEXP. Company Cancelled - 126_1
INSEXP. Company Cancelled - 126_2
stuff... ___INSEXP. Company Cancelled - 126

The problem here is that the substring INS.  is being replaced everywhere seq_num is 126 whereas we only want it done where INS.  occurs at the beginning of the target string!
So, now we try REGEXP_REPLACE:
SELECT 
  REGEXP_REPLACE(x, '(^INS)(\. )', '\1EXP\2') AS &quot;Check:&quot; 
FROM test 
WHERE seq_num = 126;

Result:
Check:
INSEXP. Company Cancelled - 126_1
INSEXP. Company Cancelled - 126_2
stuff... ___INS. Company Cancelled - 126

So, we can see that REGEXP_REPLACE changes the first two INS.  substrings, but not the last one - where it doesn't occur at the beginning of the string!
So, now for the UPDATE:
UPDATE test t SET t.x = REGEXP_REPLACE(t.x, '(^INS)(\. )', '\1EXP\2') 
WHERE t.seq_num = 126;

And then (to check - always check!):
SELECT t.* FROM test t ORDER BY t.t_id;

Then:
T_ID                                   X       SEQ_NUM
   1    INS. Company Cancelled - 127               127
   2    INS. Company Cancelled - 128               128
   3    INSEXP. Company Cancelled - 126_1          126
   4    INSEXP. Company Cancelled - 126_2          126
   5    stuff... ___INS. Company Cancelled - 126   126

Et voilà! We have replaced the correct substring from the correct part of the target string - all due to the power of regular expressions!
You should also look into the REGEXP_REPLACE() functionality (the oracle-base site is excellent for all things Oracle!). I've only recently started mastering them and it's amazing what they can let you do in a one-liner that would take tons of code otherwise.
Explanation of the regex:
Now, here we specify the beginning of the string using the ^ anchor, so the replacement only takes place at the start of the string x - this specificity can be very helpful (indeed critical, as we can see in this case) when substituting text.
The ( and ) brackets are for &quot;capturing groups&quot; of text - so we can refer to &lt;start_of_string&gt; followed directly by INS followed by . (dot is also a special character - it's a single character wildcard, so it's escaped by \).
So, the group (^INS) is represented by the place-holder \1 - remember the backslash is a special character in regexps! And the (\. ) group represents a . (full stop or period) followed by a space - because . without the backslash is a wildcard for one character in regexps!
Caveat: There will be a performance penalty to pay here - naturally enough - more complex functionality requires more processing, but I would strongly urge any database programmer/DBA &amp;c. to master regular expressions - there are many tutorials out there.
As an aside (and this is more for me really), in exploring this issue, I came across this page (again, from Tim Hall's fabulous oracle-base site) - it explains 3 different ways of updating from queries!
The one I liked the best is the MERGE (3rd method) functionality (fiddle):
MERGE INTO test tt
  USING test st
  ON (tt.t_id = st.t_id AND tt.seq_num = 126)
WHEN MATCHED THEN
  UPDATE SET tt.x = REGEXP_REPLACE(st.x, '(^INS)(\. )', '\1EXP\2');

The end result is the same!
In this snippet, tt = target_table &amp; st = source_table - of course, in this case, they're both the same and the simpler code above works - but if you're going to be UPDATEing from table_1 to table_2, you'll need mechanisms like this! A fiddle for this code is to be found here.
My second favourite was what Oracle calls an Inline View (but most of us normal folks call a sub-query) - it looks like this (fiddle here):
UPDATE
  (SELECT target_table.t_id, target_table.x, target_table.seq_num,
          REGEXP_REPLACE(source_table.x, '(^INS)(\. )', '\1EXP\2') y 
          FROM test target_table, test source_table
          WHERE target_table.t_id = source_table.t_id
          AND target_table.seq_num = 126) ilv
          
  SET ilv.x = ilv.y;

And finally, there is what the page (and Oracle?) calls the sub-query method - it  uses the EXISTS predicate thus (fiddle):
UPDATE test t1
  SET (t1.x) = 
  (
    SELECT REGEXP_REPLACE(t2.x, '(^INS)(\. )', '\1EXP\2')
    FROM test t2  
    WHERE t1.seq_num = 126 AND t1.t_id = t2.t_id
  ) WHERE EXISTS (SELECT t.t_id, t.seq_num FROM test t WHERE t.t_id = t1.t_id 
                                                       AND t.seq_num = 126);

Interestingly, there is also a performance analysis after each one, which suggest to me that none is the best in all cases and therefore we should test (with realistic datasets) our queries for performance before implementing them in production (don't we always? :-) )!
I'm giving a +1 to the question because I learned a lot from answering it - I hope you did too!