【Oracle】 sql 中的字符(串)替换与转换

转载 2016年08月30日 17:24:34

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;
            返回值
            -------
            w
odefgw

分析:该语句要将'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、PLSG,和 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会显示月份

原文链接:http://blog.csdn.net/folio/article/details/7010976

一次替换多个字符串,批量替换多个字符串

吐槽一下百度搜索,怀疑其搜索质量。 以前用java开发的小工具为例,输入关键字【一次 替换 多个字符串】,在百度搜索里竟然找不到,翻了好几页也没有,2013年在百度搜索的第一页能找到的。再试试关键字【...
  • wangjunkg
  • wangjunkg
  • 2015年10月15日 10:39
  • 7041

oracle中实现截取字符串(substr)、查找字符串位置(instr)、替换字符串(replace)

(1)oracle中实现截取字符串:substr substr(string, start_position, [length]) 其中,string是元字符串,start_position为开始位置...
  • housonglin1213
  • housonglin1213
  • 2015年12月17日 17:45
  • 16131

Oracle和Sql 日期格式与字符串格式相互转换

日期格式转换为字符类型Oracle 类型日期格式转换字符串类型1.date 转换为 字符串to_char(日期,”转换格式” ) 即把给定的日期按照“转换格式”转换。 转换的格式:表示year的:...
  • yali1990515
  • yali1990515
  • 2016年03月04日 17:59
  • 20944

Oracle数据库之SQL单行函数---字符函数之TRIM

前面笔者就提到了要将TRIM函数做一下总结的,现在就把自己做的练习记录下来      通过前面知道了TRIM函数的作用可以去除字符串的空格。例如:            selec...
  • kiritor
  • kiritor
  • 2013年04月02日 16:49
  • 1776

Oracle, 复杂SQL,记录一下,用到了转义字符,左连接技巧

select t1.name mirror_WG,t1.description, t1.workgroupid mirror_WG_ID, t2.name Main_WG,t2.workgroupid...
  • jamex
  • jamex
  • 2013年03月18日 15:55
  • 677

Oracle数据库之SQL单行函数---字符函数续

前一篇博客练习了一些字符函数,之后自己又去下查阅了相关的资料,上一篇博客中还有很多的     字符函数没有练习到,这次主要是对上次的一些未用到的字符函数进行一些补充练习。             ...
  • kiritor
  • kiritor
  • 2013年04月02日 15:19
  • 1146

ORACLE 动态SQL 转义字符 单引号

脚本内容:业务部门需要定期从某库取出质保数据:  cat zhibaodata.sh  #!/bin/sh export DISPLAY=:0 if [ -f ~/.bash_profile ];...
  • EVISWANG
  • EVISWANG
  • 2017年03月10日 10:22
  • 440

33.Oracle数据库SQL开发之 使用简单函数——使用单行函数字符函数

33.Oracle数据库SQL开发之 使用简单函数——使用单行函数-字符函数 欢迎转载,转载请标明出处:          这边开始使用数据库内置函数。函数可以接受零个或多个输入参数,并返回一个输出参...
  • notbaron
  • notbaron
  • 2015年11月08日 22:36
  • 30366

ORACLE(PL/SQL) 根据字符分割(SPLIT)字符串返回数组

ORACLE没有提供现成的函数来进行字符串的分割,网上有很多中现实方法,本文介绍一种我自己的实现方式。主要是使用Oracle内部函数instr和substr函数的方式来实现。...
  • warrenjiang
  • warrenjiang
  • 2015年11月28日 13:56
  • 5128

Oracle-21-SQL单行函数之字符函数

一、SQL函数 Oracle提供了丰富的内建函数。一个函数接受0个或多个输入值,并返回一个输出值。Oracle函数分为两组:单行函数(singlerow function)和聚合函数(aggregat...
  • wy_0928
  • wy_0928
  • 2016年04月14日 21:12
  • 3735
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:【Oracle】 sql 中的字符(串)替换与转换
举报原因:
原因补充:

(最多只允许输入30个字)