【总结】Oraclesql中的字符（串）替换与转换
1、REPLACE
语法：REPLACE(char, search_string,replacement_string)
⽤法：将char中的字符串search_string全部转换为字符串replacement_string。
举例：SQL> select REPLACE('fgsgswsgs', 'fk' ,'j') 返回值 from dual;
返回值
---------
fgsgswsgs
SQL> select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt') 返回值 from dual;
返回值
-----------------------
fgeeerrrtttsweeerrrttts
分析：第⼀个例⼦中由于'fgsgswsgs'中没有与'fk'匹配的字符串，故返回值仍然是'fgsgswsgs'；
第⼆个例⼦中将'fgsgswsgs'中的字符串'sg'全部转换为'eeerrrttt'。
2、TRANSLATE
语法：TRANSLATE(char, from, to)
⽤法：返回将出现在from中的每个字符替换为to中的相应字符以后的字符串。
若from⽐to字符串长，那么在from中⽐to中多出的字符将会被删除。
三个参数中有⼀个是空，返回值也将是空值。
举例：SQL> select translate('abcdefga','abc','wo') 返回值 from dual;
sql语句替换表中内容
返回值
-------
wodefgw
分析：该语句要将'abcdefga'中的'abc'转换为'wo',
由于'abc'中'a'对应'wo'中的'w'，故将'abcdefga'中的'a'全部转换成'w';
⽽'abc'中'b'对应'wo'中的'o'，故将'abcdefga'中的'b'全部转换成'o';
'abc'中的'c'在'wo'中没有与之对应的字符，故将'abcdefga'中的'c'全部删除;
简单说来，就是将from中的字符转换为to中与之位置对应的字符，若to中不到与之对应的字符，返回值中的该字符将会被删除。
在实际的业务中，可以⽤来删除⼀些异常数据，⽐如表a中的⼀个字段t_no表⽰电话号码，⽽电话号码本⾝应该是⼀个由数字组成的字符串，为了删除那些含有⾮数字的异常数据，就⽤到了translate函数：
举例：SQL> delete from a
where length(translate(trim(a.t_no),
'0123456789' || a.t_no,
'0123456789')) <> length(trim(a.t_no));
3、DECODE
语法：DECODE(expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)
DECODE(expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)
⽤法：⽐较表达式和搜索字，如果匹配，返回结果；
如果不匹配，返回default值；
如果未定义default值，则返回空值。
含义如下：
IF expression=search_1 THEN
RETURN(result_1);
ELSE IF expression=search_2 THEN
RETURN(result_2);
......
ELSE IF expression=search_n THEN
RETURN(result_n);
ELSE
IF ISEXIXT(default)
RETURN(default);
ELSE
RETURN(NULL);
END IF;
举例：SQL> select checkup_person 投票⼈,
decode(checkup_result,’Y’,’赞同票’,’N’,’反对票’,NULL,’弃权票’,’⽆效票’) 投票结果
FROM checkup;
分析：该语句要checkup表中的checkup_result字段进⾏翻译，
将‘Y’翻译为‘赞同票’；
将’N’翻译为’反对票’；
将NULL翻译为’弃权票’；
默认值是’⽆效票’。
4、Oracle 格式化函数 to_char To_date To_number
格式化函数提供⼀套有效的⼯具⽤于把各种数据类型（⽇期/时间，int，float，numeric）转换成格式化的字符串以及反过来从格式化的字符串转换成原始的数据类型。
注意：所有格式化函数的第⼆个参数是⽤于转换的模板。
4-1、格式化函数
函数返回描述例⼦
to_char(timestamp, text)text把 timestamp 转换成 string to_char(timestamp 'now','HH12:MI:SS')
to_char(int, text)text把 int4/int8 转换成 string to_char(125, '999')
to_char(float, text)text把 float4/float8 转换成 string to_char(125.8, '999D9')
to_char(numeric, text)text把 numeric 转换成 string to_char(numeric '-125.8', '999D99S')
to_date(text, text)date把 string 转换成 date to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)date把 string 转换成 timestamp to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)numeric把 string 转换成 numeric to_number('12,454.8-', '99G999D9S')
4-2、⽤于 date/time 转换的模板
模板描述
HH⼀天的⼩时数 (01-12)
HH12⼀天的⼩时数 (01-12)
HH24⼀天的⼩时数 (00-23)
MI分钟 (00-59)
SS秒 (00-59)
SSSS午夜后的秒 (0-86399)
AM or A.M. or PM or P.M.正午标识（⼤写）
am or pm 正午标识（⼩写）
Y,YYY带逗号的年（4 和更多位）
YYYY年（4和更多位）
YYY年的后三位
YY年的后两位
Y年的最后⼀位
BC or B.C. or AD or A.D.年标识（⼤写）
bc or ad or a.d.年标识（⼩写）
MONTH全长⼤写⽉份名（9字符）
Month全长混合⼤⼩写⽉份名（9字符）
month全长⼩写⽉份名（9字符）
MON⼤写缩写⽉份名（3字符）
Mon缩写混合⼤⼩写⽉份名（3字符）
mon⼩写缩写⽉份名（3字符）
MM⽉份 (01-12)
DAY全长⼤写⽇期名（9字符）
Day全长混合⼤⼩写⽇期名（9字符）
day全长⼩写⽇期名（9字符）
DY缩写⼤写⽇期名（3字符）
Dy缩写混合⼤⼩写⽇期名（3字符）
dy缩写⼩写⽇期名（3字符）
DDD⼀年⾥的⽇⼦(001-366)
DD⼀个⽉⾥的⽇⼦(01-31)
D⼀周⾥的⽇⼦(1-7；SUN=1)
W⼀个⽉⾥的周数
⼀年⾥的周数
世纪（2 位）
Julian ⽇期（⾃公元前4712年1⽉1⽇来的⽇期）
季度
罗马数字的⽉份（I-XII；I=JAN）－⼤写
罗马数字的⽉份（I-XII；I=JAN）－⼩写
（所有模板都都允许使⽤前缀和后缀修改器。模板⾥总是允许使⽤修改器。前缀　'FX' 只是⼀个全局修改器）
4-3、⽤于⽇期/时间模板 to_char() 的后缀
后缀描述例⼦
FM填充模式前缀FMMonth
TH⼤写顺序数后缀DDTH
th⼩写顺序数后缀DDTH
FX固定模式全局选项（见下⾯）FX Month DD Day
SP拼写模式（还未实现）DDSP
⽤法须知：
a、如果没有使⽤ FX 选项，to_timestamp 和 to_date 忽略空⽩。FX 必须做为模板⾥的第⼀个条⽬声明。
b、反斜杠（"\"）必须⽤做双反斜杠（"\\"），例如 '\\HH\\MI\\SS'。
c、双引号（'"'）之间的字串被忽略并且不被分析。如果你想向输出写双引号，你必须在双引号前⾯放置⼀个双反斜杠（'\\'），例如 '\\"YYYY Month\\"'。
d、to_char ⽀持不带前导双引号（'"'）的⽂本，但是在双引号之间的任何字串会被迅速处理并且还保证不会被当作模板关键字解释（例
如：'"Hello Year: "YYYY'）。
4-4、⽤于 to_char(numeric) 的模板
模板描述
9带有指定位数的值
0前导零的值
. （句点）⼩数点
, （逗号）分组（千）分隔符
PR尖括号内负值
S带负号的负值（使⽤本地化）
L货币符号（使⽤本地化）
D⼩数点（使⽤本地化）
G分组分隔符（使⽤本地化）
MI在指明的位置的负号（如果数字 < 0）
PL在指明的位置的正号（如果数字 > 0）
SG在指明的位置的正/负号
RN罗马数字（输⼊在 1 和 3999 之间）
TH or th转换成序数
V移动n位（⼩数）（参阅注解）
EEEE科学记数。现在不⽀持。
⽤法须知：
a、使⽤ 'SG'，'PL' 或 'MI' 的带符号字并不附着在数字上⾯；例如，to_char(-12, 'S9999') ⽣成' -12'，⽽ to_char(-12, 'MI9999') ⽣成'- 12'。Oracle⾥的实现不允许在9前⾯使⽤MI，⽽是要求9在MI前⾯。
b、PL，SG，和TH是 Postgres 扩展。
c、9表明⼀个与在9字串⾥⾯的⼀样的数字位数。如果没有可⽤的数字，那么使⽤⼀个空⽩（空格）。
d、TH不转换⼩于零的值，也不转换⼩数。TH是⼀个 Postgres 扩展。
e、V⽅便地把输⼊值乘以10^n，这⾥n是跟在V后⾯的数字。to_char不⽀持把V与⼀个⼩数点绑在⼀起使⽤（例如. "99.9V99" 是不允许的）。
4-5、to_char 例⼦
输⼊输出
to_char(now(),'Day, HH12:MI:SS')'Tuesday , 05:39:18'
to_char(now(),'FMDay, HH12:MI:SS')'Tuesday, 05:39:18'
to_char(-0.1,'99.99')' -.10'
to_char(-0.1,'FM9.99')'-.1'
to_char(0.1,'0.9')' 0.1'
to_char(12,'9990999.9')' 0012.0'
to_char(12,'FM9990999.9')'0012'
to_char(485,'999')' 485'
to_char(-485,'999')'-485'
to_char(485,'9 9 9')' 4 8 5'
to_char(1485,'9,999')' 1,485'
to_char(1485,'9G999')' 1 485'
to_char(148.5,'999.999')' 148.500'
to_char(148.5,'999D999')' 148,500'
to_char(3148.5,'9G999D999')' 3 148,500'
to_char(-485,'999S')'485-'
to_char(-485,'999MI')'485-'
to_char(485,'999MI')'485'
'+485'
'+485'
'-485'
'4-85'
'<485>'
'DM 485
' CDLXXXV'
'CDLXXXV'
V
' 482nd'
'Good number: 485'
'Pre-decimal: 485 Post-decimal: .800'
' 12000'
' 12400'
' 125'
注意：
1.在使⽤Oracle的to_date函数来做⽇期转换时，可能会直觉地采⽤“yyyy-MM-dd HH:mm:ss”的格式作为格式进⾏转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。如：select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual；原因是SQL中不区分⼤⼩写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采⽤了mi代替分钟。select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
2.另要以24⼩时的形式显⽰出来要⽤HH24
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//mi是分钟
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm会显⽰⽉份
5、正则表达式转换（待续......）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。