是否可以使用 SQL 更新 CLOB 内的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3139905/
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
Is it possible to update data inside a CLOB using SQL?
提问by Hemant
I have a table having one clob column which has XML data in it. Say I want to replace XYZ with ABC in the clob column. Is it possible using sqlplus?
我有一个表，其中有一个 clob 列，其中包含 XML 数据。假设我想在 clob 列中用 ABC 替换 XYZ。是否可以使用 sqlplus？
回答by N. Gasparotto
Why not try it ?
为什么不试试呢？
SQL> create table nnn(c1 clob);
Table created.
SQL> insert into nnn values ('text ABC end');
1 row created.
SQL> select * from nnn;
C1
-------------------------------------------------
text ABC end
SQL> update nnn set c1=replace(c1,'ABC','XYZ');
1 row updated.
SQL> select * from nnn;
C1
-------------------------------------------------
text XYZ end
SQL>
回答by APC
"i have new line in the column. any advice?"
“我在列中有新行。有什么建议吗？”
Newlines are characters; if you want to amend text which contains them you need to include them in the search string. You can do this using the CHR() which takes an ASCII value as an argument. The precise codes you need to include vary according to OS. Because I ran this example on MS Windows I needed to pass both linefeed (ASCII=10) and carriage return (ASCII=13).
换行符是字符；如果您想修改包含它们的文本，您需要将它们包含在搜索字符串中。您可以使用将 ASCII 值作为参数的 CHR() 来执行此操作。您需要包含的精确代码因操作系统而异。因为我在 MS Windows 上运行这个例子，所以我需要同时传递换行符 (ASCII=10) 和回车符 (ASCII=13)。
SQL> select * from t42
2 /
TXT
--------------------------------------------------------------------------------
<ABC> ABCD
</ABC>
SQL> update t42 set txt=replace(txt,'ABCD'||chr(10)||chr(13), 'APC woz here')
2 /
1 row updated.
SQL> select * from t42
2 /
TXT
--------------------------------------------------------------------------------
<ABC> APC woz here </ABC>
SQL>
Incidentally, if you are storing XML text it might be worthwhile using the XMLType datatype for the column instead of CLOB. It comes with a lot of useful functionality.
顺便说一下，如果您要存储 XML 文本，那么对于列使用 XMLType 数据类型而不是 CLOB 可能是值得的。它带有许多有用的功能。
回答by Jedi1965
Yes, it's possible with one REPLACE() function. Try:
是的，可以使用一个 REPLACE() 函数。尝试：
update nnn set c1 = REPLACE(c1,'ABC>','XYZ>')
