How to replace part of string in a column, in oracle

Question

I am using oracle, toad.

I want to replace , with backspace.

Column consists of: bla bla foo ,CALL HELPDESK

It has to replace: bla bla foo CALL HELPDESK

Basically , should be removed

I tried like this:

UPDATE Mytable t
   SET column = REPLACE(t.U_MSG, ''%, CALL HELPDESK'', '% CALL HELPDESK')

Answer 1

REPLACE doesn't use wildcards, it simply replaces all instances of the first string with the second string. This should work:

UPDATE Mytable t
   SET column = REPLACE(t.U_MSG, ', CALL HELPDESK', ' CALL HELPDESK')

Answer 2

As the replaced string is fixed why not simply use that:

UPDATE Mytable t
    SET column = SUBSTR(t.U_MSG, 1, LENGTH(t.U_MSG)-15)
--                                                  ^^
--                                    length of the replaced string
--                                    hard coded in this example, should
--                                    probably use `LENGTH(...)` for ease
--                                    of maintenance in production code

This is probably less clever than other solutions, but this will work even if, by unexpected twist of fate, the replaced string is present several times in some of your strings:

WITH t AS (
  SELECT 'PLEASE, CALL HELPDESK' U_MSG FROM DUAL
  UNION ALL SELECT 'CALL HELPDESK, CALL HELPDESK! THEY SAID, CALL HELPDESK' FROM DUAL
)

SELECT SUBSTR(t.U_MSG, 1, LENGTH(t.U_MSG)-15) || ' CALL HELP DESK' MSG FROM t; 

Producing:

MSG
------------------------------------------------------
PLEASE CALL HELP DESK
CALL HELPDESK, CALL HELPDESK! THEY SAID CALL HELP DESK

Answer 3

Considering not to replace ALL commas..

UPDATE Mytable t
   SET column = REGEXP_REPLACE(t.U_MSG,',( )*(CALL HELPDESK)','CALL HELPDESK')

Else, Simply

UPDATE Mytable t
   SET column = REGEXP_REPLACE(t.U_MSG,',( )*')

Note, It removes all spaces after comma as well.