将 REPLACE 字符串函数与 TRIM 一起使用 (Oracle/PLSQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23296068/
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
Using REPLACE string function with TRIM (Oracle/PLSQL)
提问by Huskie69
I have a problem with a 3rd party product I use which handles external XML files. It causes the database to crash as a result of not being able to parse ampersands correctly (they're not escaped).
我使用的处理外部 XML 文件的 3rd 方产品有问题。由于无法正确解析＆符号（它们没有被转义），它会导致数据库崩溃。
If I was to do this via SQLPlus I'd simply use 'SET DEFINE OFF' or 'SET ESCAPE ON' or both!
如果我要通过 SQLPlus 执行此操作，我只需使用“SET DEFINE OFF”或“SET ESCAPE ON”或两者都使用！
However, this process calls a function from a package.
但是，此过程会从包中调用函数。
Part of the body of the package is as follows:
部分包体如下：
CURSOR extractxml_c
IS
SELECT TRIM( extractvalue(column_value, '/TARLROW/HUSID')) husid,
TRIM( extractvalue(column_value, '/TARLROW/UKPRN')) ukprn,
TRIM( extractvalue(column_value, '/TARLROW/NUMHUS')) numhus,
TRIM( extractvalue(column_value, '/TARLROW/CAMPID')) campid,
TRIM( extractvalue(column_value, '/TARLROW/NAMECAT')) namecat,
TRIM( extractvalue(column_value, '/TARLROW/COURSEAIM')) courseaim,
TRIM( extractvalue(column_value, '/TARLROW/CTITLE')) ctitle,
TRIM( extractvalue(column_value, '/TARLROW/OWNSTU')) ownstu,
TRIM( extractvalue(column_value, '/TARLROW/STATUS') ) status,
TRIM( extractvalue(column_value, '/TARLROW/YEAR')) year,
TRIM( extractvalue(column_value, '/TARLROW/QUALENT2')) qualent2,
TRIM( extractvalue(column_value, '/TARLROW/COMDATE')) comdate,
TRIM( extractvalue(column_value, '/TARLROW/POSTCODE')) postcode,
TRIM( extractvalue(column_value, '/TARLROW/ETHNIC')) ethnic,
TRIM( extractvalue(column_value, '/TARLROW/DOMICILE')) domicile,
TRIM( extractvalue(column_value, '/TARLROW/YRLLINST') ) yrllinst,
TRIM( extractvalue(column_value, '/TARLROW/UCASAPPID')) ucasappid,
TRIM( extractvalue(column_value, '/TARLROW/OWNINST')) owninst,
TRIM( extractvalue(column_value, '/TARLROW/TTCID') ) ttcid,
TRIM( extractvalue(column_value, '/TARLROW/QUALENT3')) qualent3,
NULL pidm,
p_cohort chrt_code,
p_term_code term_code,
USER user_id,
SYSDATE activity_date,
'Banner: ESC_HST_CHRT_HINTAR_XML' data_origin,
NULL surrogate_id,
NULL version,
NULL vpdi_code,
gkkpsql.API_RetrieveRunSequence run_seq
FROM TABLE(XMLSequence( l_xmltype.extract('/TARLREPORT/TARLROWS/TARLROW'))) ;
I'd like to replace the ampersand characters pulled from the XML file using the replace function at the CTITLE section in the above code.
我想使用上面代码中 CTITLE 部分的替换函数替换从 XML 文件中提取的 & 字符。
Something like this:
像这样的东西：
SELECT REPLACE('&' , '|| chr(38) || ')
However, I can't seem to get the syntax correct. On package compilation I keep getting missing right parenthesis errors.
但是，我似乎无法获得正确的语法。在包编译时，我一直缺少右括号错误。
I've used:
我用过：
TRIM( extractvalue(column_value, (REPLACE('&' , '|| chr(38) || ') '/TARLROW/CTITLE')) ctitle,
and
和
REPLACE('&' , '|| chr(38) || ') TRIM( extractvalue(column_value, '/TARLROW/CTITLE')) ctitle,
and neither are working.
两者都不起作用。
Any ideas how I'd replace characters pulled from a TRIM( extractvalue ) string function?
任何想法如何替换从 TRIM(extractvalue) 字符串函数中提取的字符？
TIA
TIA
回答by Alex Poole
REPLACEtakes three arguments; the optional third one is the replacement string. The first is the original string that you want to transform. So I think you want:
REPLACE需要三个参数；可选的第三个是替换字符串。第一个是您要转换的原始字符串。所以我认为你想要：
REPLACE(TRIM(extractvalue(column_value, '/TARLROW/CTITLE')),
'&', chr(38)) ctitle
You don't need the concatenation characters (||) as far as I can tell, unless you're doing something else with this later. The REPLACE and TRIM can go in either order, but one needs to be inside the other.
||据我所知，您不需要连接字符 ( ) ，除非您稍后对此进行其他处理。REPLACE 和 TRIM 可以按任一顺序进行，但一个需要在另一个内部。
Not sure what you mean about it causing the database to cash though. This is just a string. It's only treated as a substitution variable if you're using the ampersand in an SQL*Plus command. And your final string will still end up with an ampersand in it. It sounds like you're trying to solve the wrong problem.
不确定你的意思是什么导致数据库变现。这只是一个字符串。如果您在 SQL*Plus 命令中使用与号，它只会被视为替代变量。你的最后一个字符串仍然会以一个＆符号结尾。听起来您正在尝试解决错误的问题。
