oralce 函数总结

oracle有很多巧妙的函数可以使用，在开发过程中遇到的函数为了下次方便找寻，所以在此处记录一下。

本文目录：

1、regexp_substr() 　　截取字符串

2、regexp_like() 　　正则匹配

3、decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值) 　　转换

4、round(a,b)　　计算百分比

5、sum(case when 条件 then 1 else 0 end) as 累加值 　　累计满足条件的查询结果

6、wm_concat(a||','||b)　　拼接多条数据，如查询除三条语句，将某列字段拼接成一条输出

7、translate(expr, from_string, to_string)　　替换字符串中的字符

8、replace(char, search_string [, replace_string])　　替换字符

正文：

1、regexp_substr()

截取字符串，查询的时候可能有需要这样查：

SELECT s.org_no, regexp_substr(s.consinfo, '[^$]+', 1, 1) AS cons_no,
             regexp_substr(s.consinfo, '[^$]+', 1, 2) AS cons_name
    FROM (SELECT o.org_no, o.org_name,
                                (SELECT a.cons_no || '$' || a.cons_name
                                        FROM c_cons a
                                     WHERE rownum = 1) consinfo
                     FROM o_org o) s

查询结果：

||'$'|| 的意思是将查询的结果用 $ 符号拼接起来

语法：regexp_substr(expression, regular-expression, start-offset, end-offset)；

expression——被搜索的字符串
regular-expression——尝试匹配的模式，字符截取正则表达式
start-offset——开始搜索时相对于 expression 的偏移。start-offset 以正整数表示，表示从字符串的左端数的字符数。缺省值为 1（字符串的起点）
end-offset——获取expression 被指定字符截取后的第几个

SQL中 regexp_substr(s.consinfo, '[^$]+', 1, 1) 这一值得说的就是后边的两个1，一个1 不用管，就是一个个找匹配结果的意思，第二1 就是匹配找到的第几个

2、regexp_like()

oracle中判断某个字段是否含有字母：
select * from taccount t where regexp_like(t.vc_code, '[a-zA-Z]')

语法：REGEXP_LIKE(x,pattern)函数的功能类似于like运算符，用于判断源字符串是否匹配

或包含指定模式的子串。 x指定源字符串， pattern是正则表达式字符串。该函数只可用在where子句中。

3、decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值)

当处理分数的时候，秉持分母不能为0的原则，如果分母为0则输出0

如果我们直接这样写：

SELECT a.codeA / a.codeB AS testnum
    FROM c_cons a

分母a.codeB为空或者0时报错：

改进：

SELECT decode(a.codeB,null,0,0,0,a.codeA/a.codeB) AS testnum
    FROM c_cons a

当a.codeB为空或者0时，直接输出0，否则输出a.codeA/a.codeB。

4、round(a,b)

计算百分比，参数a 是小数，参数b是保留小数点后几位，可以和decode()函数连用

例子：round(decode(a.codeB,null,0,0,0,a.codeA/a.codeB),2) 保留小数点后两位

5、sum(case when 条件 then 1 else 0 end) as 累加值

需求：实现对所有成绩为90分的人进行一个统计

由于count中无法使用条件语句,所以,此处使用sum而不用count；

sum(case when when score = "90" then 1 else 0 end) as 累加值

then 后边就是累加的字段，写1的话就是满足条件的条数，写字段的话就是满足条件的字段累加，
else 0 就是不满足条件的累加0
end 不能忘记写，不然会报错！

6、wm_concat(a||','||b)

拼接多条数据，如查询除三条语句，将某列字段拼接成一条输出。

举个例子

 select to_char(wm_concat(d.name||','||tact.mobile)) 
 from C_CONTACT tact,p_code d
  where  tact.contact_mode = d.value
  and d.code_type = 'contactMode'
  and tact.cust_id in('2216731')

输出结果：电气联系人,12,户主,15158010642,业务办理联系人,,租户,18395920841,租户,18339960377,帐务联系人,15072406990

当然也能直接用 ||'，'||方式来写

7、translate(expr, from_string, to_string)

替换字符串中的字符.

直接上例子：

select translate('abcdefgh','abc' ,'123') from dual;
--a替换为1,b替换为2,c替换为3
结果：123defgh

select translate('abcdefgh','abc' ,'') from dual;
--如果替换字符整个为空字符，则直接返回null
结果：null

select translate('abccdefgh','&abc' ,'&') from dual;
--如果想筛掉对应字符，应传入一个不相关字符，同时替换字符也加一个相同字符；
结果：defgh

select translate('abcdefgh','abcc' ,'1234') from dual;
--如果相同字符对应多个字符，按第一个
结果：1233defgh;

select translate('0中华人民6共和国6万岁8','#0123456789' ,'#') from dual;
--将原始字符串中的所有数字删除
结果：中华人民共和国万岁

如果想筛掉汉字保留数字，则先把数字筛选掉，再用筛选出的汉字去筛选原来的语句留下数字

select translate('0中华人民6共和国6万岁8','#'||translate('0中华人民6共和国6万岁8','#0123456789','#'),'#') from dual;
结果：0668

translate(expr, from_string, to_string)
含义：translate返回expr值，其中出现在from_string中的每个字符都会被to_string中的相应字符替换掉。

8、replace(char, search_string [, replace_string])

替换字符，

直接上例子：

select replace('0123456789','0','a') from dual;
--0替换成a
结果：a123456789

select replace('0123456789','0','') from dual;
--0替换成null
结果：123456789

select replace('0123456789','0') from dual;
--当replace_string不指定时，则会将char中与search_string相同的字符删掉
结果：123456789

select replace('0123456789','012',,'abcd') from dual;
--将字符串整体换掉，而不是单个字符换掉

结果：abcd3456789

replace(char, search_string [, replace_string])
含义：replace返回char值，其中会将char中与search_string相同的字符串全部替换成replace_string字符串。

总结：replace与translate都是替代函数，只不过replace针对的是字符串，将对应的字符串整体替换；而translate针对的是单个字符，只是替换对应的字符。

oracle函数