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;
返回值
-------
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 a.m. or pm or p.m.
正午标识(小写)
Y,YYY
带逗号的年(4 和更多位)
YYYY
年(4和更多位)
YYY
年的后三位
YY
年的后两位
Y
年的最后一位
BC or B.C. or AD or A.D.
年标识(大写)
bc or b.c. 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
一个月里的周数
WW
一年里的周数
CC
世纪(2 位)
J
Julian 日期(自公元前4712年1月1日来的日期)
Q
季度
RM
罗马数字的月份(I-XII；I=JAN)－大写
rm
罗马数字的月份(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‘
to_char(485,‘PL999‘)
‘+485‘
to_char(485,‘SG999‘)
‘+485‘
to_char(-485,‘SG999‘)
‘-485‘
to_char(-485,‘9SG99‘)
‘4-85‘
to_char(-485,‘999PR‘)
‘<485>‘
to_char(485,‘L999‘)
‘DM 485
to_char(485,‘RN‘)
‘ CDLXXXV‘
to_char(485,‘FMRN‘)
‘CDLXXXV‘
to_char(5.2,‘FMRN‘)
V
to_char(482,‘999th‘)
‘ 482nd‘
to_char(485, ‘"Good number:"999‘)
‘Good number: 485‘
to_char(485.8,‘"Pre-decimal:"999" Post-decimal:" .999‘)
‘Pre-decimal: 485 Post-decimal: .800‘
to_char(12,‘99V999‘)
‘ 12000‘
to_char(12.4,‘99V999‘)
‘ 12400‘
to_char(12.45, ‘99V9‘)
‘ 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、正则表达式转换(待续......)
1491
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
