oracle 的两个替换函数 REPLACE TRANSLATE

本文详细解释了REPLACE和TRANSLATE两个函数的区别与用法。REPLACE用于全局替换字符串中的某个子串,而TRANSLATE则实现字符级别的逐一替换。通过多个实例展示了这两种字符串操作在实际应用中的效果。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

replace就是一般意义上的字符串替换,translate只是一个字符替换对应的一个字符,下面有手册说明,还有例子 O(∩_∩)O哈哈~

REPLACE


Syntax
Description of replace.gif follows
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 datatypes 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

TRANSLATE


Syntax
Description of translate.gif follows

Purpose
TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string.
Characters in expr that are not in from_string are not replaced. If expr is a character string, then you must enclose it in single
quotation marks. The argument from_string can contain more characters than to_string. In this case, the extra characters at the
end of from_string have no corresponding characters in to_string. If these extra characters appear in char, then they are removed
from the return value.
You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle Database
interprets the empty string as null, and if this function has a null argument, then it returns null.
TRANSLATE provides functionality related to that provided by the REPLACE function. REPLACE lets you substitute a single string for
another single string, as well as remove character strings. TRANSLATE lets you make several single-character, one-to-one substitutions in one operation.
This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.
See Also:

"Datatype Comparison Rules" for more information and REPLACE

Examples
The following statement translates a book title into a string that could be used (for example) as a filename.
The from_string contains four characters: a space, asterisk, slash, and apostrophe (with an extra apostrophe as the escape character).
The to_string contains only three underscores. This leaves the fourth character in the from_string without a corresponding replacement,
so apostrophes are dropped from the returned value.
SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;

TRANSLATE('SQL*PLUSU
--------------------
SQL_Plus_Users_Guide

看看例子更好明白:

TRANSLATE     ('abcd',     'ab',     '12')     ==>     '12cd'   
    TRANSLATE     ('12345',     '15',     'xx')     ==>     'x234x'   
    TRANSLATE     ('grumpy     old     possum',     'uot',     '%$*')     ==>         'gr%mpy     $ld     p$ss%m'   
    TRANSLATE     ('my     language     needs     the     letter     e',     'egms',     'X')     
    ==>   
          'y     lanuaX     nXXd     thX     lXttXr     X';   
    TRANSLATE     ('please     go     away',     'a',     NULL)     ==>     NULL   
    
    REPLACE     ('CAT     CALL',     'C')     ==>     'AT     ALL'   
    REPLACE     ('Zero     defects     in     period     99     reached     99%!',     '99',     '100')     
    ==>   
                'Zero     defects     in     period     100     reached     100%!'   
    REPLACE     ('this     that     and     the     other',     'th',     'z')     ==>     'zis     zat     and     ze     ozer'  

 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值