Oracle函数使用收集

---

listagg

作用：
可以实现将多列记录聚合为一列记录，实现数据的压缩。

语法：
listagg(measure_expr，delimiter) within group ( order by order_by_clause);
解释：measure_expr可以是基于任何列的表达式；delimiter分隔符，默认为NULL；order_by_clause决定了列值的拼接顺序。

举例：

---

translate

translate(expr, from_string, to_string)
–from_string与to_string以字符为单位，对应字符一一替换
select translate(‘ab123bcadefg’, ‘abcdefg’, ‘1234567’) as str from dual

可以用translate把字符串中的数字 和空格替换为空
select translate(‘213113 3123 asasd11’,’- 0123456789’, ‘-’)from dual;

---

extract

oracle中extract()函数----用于截取年、月、日、时、分、秒

--只可以从一个date类型中截取年月日
--从timestamp中获取年月日时分秒
select 
sysdate
,systimestamp
,extract(year from sysdate) year
,extract(month from sysdate) month
,extract(day from sysdate) day
,extract(year from systimestamp) year
,extract(month from systimestamp) month
,extract(day from systimestamp) day
,to_char(sysdate,'hh24')hour 
,to_char(systimestamp,'hh12')hour12
,extract(minute from systimestamp) minute
,extract(second from systimestamp) second
,extract(timezone_hour from systimestamp) th
,extract(timezone_minute from systimestamp) tm
,extract(timezone_region from systimestamp) tr
,extract(timezone_abbr from systimestamp) ta
from dual

--获取两个日期相同时间段之间的具体时间间隔
select
extract (day from dt2 - dt1) day,
extract (hour from dt2 - dt1) hour,
extract (minute from dt2 - dt1) minute,
extract (second from dt2 - dt1) second
from
(
select
to_timestamp ('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1,
to_timestamp ('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2
from
dual
)

---

trim，ltrim，rtrim

TRIM去除指定字符的前后空格
LTRIM去除指定字符的前面空格
RTRIM去除指定字符后面后空格
SELECT TRIM(’ aa aa ‘) FROM dual;
SELECT LTRIM(’ bb bb’) FROM dual;
SELECT RTRIM(’ cc cc ') FROM dual;

---

UPPER，LOWER

小写转大写UPPER
大写转小写LOWER
SELECT UPPER(‘aa aa AA’) FROM dual;
SELECT LOWER(‘bb bb BB’) FROM dual;