oracle 如何在oracle中替换列中的部分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26384758/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to replace part of string in a column, in oracle
提问by niru dyogi
I am using oracle, toad.
我正在使用oracle，蟾蜍。
I want to replace , with backspace.
我想用退格键替换 , 。
Column consists of: bla bla foo ,CALL HELPDESK
列包括： bla bla foo ,CALL HELPDESK
It has to replace: bla bla foo CALL HELPDESK
它必须替换： 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')
回答by Allan
REPLACEdoesn't use wildcards, it simply replaces all instances of the first string with the second string. This should work:
REPLACE不使用通配符，它只是用第二个字符串替换第一个字符串的所有实例。这应该有效：
UPDATE Mytable t
SET column = REPLACE(t.U_MSG, ', CALL HELPDESK', ' CALL HELPDESK')
回答by Maheswaran Ravisankar
Considering notto replace ALLcommas..
考虑不替换所有逗号..
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.
注意，它也会删除逗号后的所有空格。
回答by Sylvain Leroux
As the replaced string is fixedwhy 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
