Skip to main content
edited body
Source Link
Sylvain Leroux
  • 51.9k
  • 8
  • 111
  • 133

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
--                                    onof 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

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
--                                    on 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

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
Source Link
Sylvain Leroux
  • 51.9k
  • 8
  • 111
  • 133

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
--                                    on 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