【总结】Oracle sql 中的字符（串）替换与转换

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 转换成 stringto_char(timestamp 'now','HH12:MI:SS')to_char(int, text)text把 int4/int8 转换成 stringto_char(125, '999')to_char(float, text)text把 float4/float8 转换成 stringto_char(125.8, '999D9')to_char(numeric, text)text把 numeric 转换成 stringto_char(numeric '-125.8', '999D99S')to_date(text, text)date把 string 转换成 dateto_date('05 Dec 2000', 'DD Mon YYYY')to_timestamp(text, text)date把 string 转换成 timestampto_timestamp('05 Dec 2000', 'DD Mon YYYY')to_number(text, text)numeric把 string 转换成 numericto_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 位）JJulian 日期（自公元前4712年1月1日来的日期）Q季度RM罗马数字的月份（I-XII；I=JAN）－大写rm罗马数字的月份（I-XII；I=JAN）－小写

（所有模板都都允许使用前缀和后缀修改器。模板里总是允许使用修改器。前缀　'FX' 只是一个全局修改器）

4-3、用于日期/时间模板 to_char() 的后缀

后缀描述例子FM填充模式前缀FMMonthTH大写顺序数后缀DDTHth小写顺序数后缀DDTHFX固定模式全局选项（见下面）FX Month DD DaySP拼写模式（还未实现）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 485to_char(485,'RN')' CDLXXXV'to_char(485,'FMRN')'CDLXXXV'to_char(5.2,'FMRN')Vto_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、正则表达式转换（待续......）

出处：http://blog.csdn.net/folio/article/details/7010976