Oracle 使用TRANSLATE函数替换字符串中的字符

一个需求，如何将(1234,56789,12345)替换为'1234*56789*12345'?

第一种方式,使用replace:

ChenZw> SELECT REPLACE(REPLACE(REPLACE('(1234,56789,12345)',',','*'),'(',''''),')','''') FROM DUAL;

REPLACE(REPLACE(RE

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

'1234*56789*12345'

已选择 1 行。

上面的代码明显比较粗鲁,下面看一下Replace函数的官方介绍:

Purpose

REPLACEreturnscharwith every occurrence ofsearch_stringreplaced withreplacement_string. Ifreplacement_stringis omitted or null, then all occurrences ofsearch_stringare removed. Ifsearch_stringis null, thencharis returned.

Bothsearch_stringandreplacement_string, as well aschar, can be any of the data typesCHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB, orNCLOB. The string returned is in the same character set aschar. The function returnsVARCHAR2if the first argument is not a LOB and returnsCLOBif the first argument is a LOB.

Examples

The following example replaces occurrences ofJwithBL:

SELECT REPLACE('JACK and JUE','J','BL') "Changes"
     FROM DUAL;

Changes
--------------
BLACK and BLUE

第二种方式,使用translate函数:

ChenZw> SELECT TRANSLATE('(1234,56789,12345)','(,)','''*''') AS STR FROM DUAL;

STR

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

'1234*56789*12345'

已选择 1 行。

使用这个函数处理这个问题就看起来比较舒服一点,至少没有replace那么暴力,看一下官方关于translate函数的介绍:

Purpose

TRANSLATEreturnsexprwith all occurrences of each character infrom_stringreplaced by its corresponding character into_string. Characters inexprthat are not infrom_stringare not replaced. The argumentfrom_stringcan contain more characters thanto_string. In this case, the extra characters at the end offrom_stringhave no corresponding characters into_string. If these extra characters appear inexpr, then they are removed from the return value.

If a character appears multiple times infrom_string, then theto_stringmapping corresponding to the first occurrence is used.

You cannot use an empty string forto_stringto remove all characters infrom_stringfrom the return value. Oracle Database interprets the empty string as null, and if this function has a null argument, then it returns null. To remove all characters infrom_string, concatenate another character to the beginning offrom_stringand specify this character as theto_string. For example,TRANSLATE(expr, 'x0123456789', 'x') removes all digits fromexpr.

TRANSLATEprovides functionality related to that provided by theREPLACEfunction.REPLACElets you substitute a single string for another single string, as well as remove character strings.TRANSLATElets you make several single-character, one-to-one substitutions in one operation.

This function does not supportCLOBdata directly. However,CLOBs can be passed in as arguments through implicit data conversion.

Examples

The following statement translates a book title into a string that could be used (for example) as a filename. Thefrom_stringcontains four characters: a space, asterisk, slash, and apostrophe (with an extra apostrophe as the escape character). Theto_stringcontains only three underscores. This leaves the fourth character in thefrom_stringwithout 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

作者 陈字文（热衷于PM\ORACLE\JAVA等，欢迎同行交流):ziwen#163.com 扣扣:4零9零2零1零零