SQL替换一列中的两个字符串

Question

我想替换一个字符串中的两个字符。

代替

Name 
I am not aware of any or potential that hasn’t yet been reported 

Name 
I am not aware of any/potential that hasn't yet been reported 

我使用了这个查询:

replace(Replace(Name, ' or ', '/'), '’', ''')

但是我得到一个错误

ORA-01756:引号字符串未正确终止

Answer 1

我真的不明白你想用你的双替换做什么。你可以只用一个:

SELECT REPLACE(name,' or ','/') newstring FROM yourstrings;

如果你真的需要双替换，你也可以这样做:

SELECT REPLACE(REPLACE(name,' or ','/'),'’','''') newstring FROM yourstrings;

更有趣的是如何插入包含撇号，引号等的字符串。您可以使用q操作符来完成，例如:

INSERT INTO yourstrings VALUES 
(q'[I am not aware of any or potential that hasn’t yet been reported ]');

请参阅下面的工作示例:db<>fiddle

Answer 2

select replace(replace
('I am not aware of any or potential that hasn’t yet been reported',' or ','/'),
'’','''') 
from dual;

此处参考溶液