使用Oracle REPLACE函数替换查询结果中的指定字符（ReplacesubstringinOracle）的调试经验分享

需求：

查询出来的字段中带有一些前缀，用户不希望在报表中显示这些前缀。目前的操作方法是在excel中手动替换掉。我在想，有没有办法自动化？使用vba有点小题大做，能不能放到sql中实现呢？可以，使用replace函数即可。

代码：

replace (utable.ufield, 'not_needed','')

顺便了解以下oracle database sql language reference中对replace函数的描述：

-----------------------------------------------------------------------------------------------------------------

purpose

replace returns char with every occurrence of search_string replaced with

replacement_string. if replacement_string is omitted or null, then all occurrences of

search_string are removed. if search_string is null, then char is returned.

both search_string and replacement_string, as well as char, can be any of the data

types char, varchar2, nchar, nvarchar2, clob, or nclob. the string returned is in the

same character set as char. the function returns varchar2 if the first argument is not a

lob and returns clob if the first argument is a lob.

replace provides functionality related to that provided by the translate function.

translate provides single-character, one-to-one substitution. replace lets you

substitute one string for another as well as to remove character strings.

examples

the following example replaces occurrences of j with bl:

select replace('jack and jue','j','bl') "changes"

from dual;

changes

--------------

black and blue