1.trim()
select trim(leading 's' from 'ssxbybhs') from dual
返回xbybhs
trim(leadeing 截取字符X from 字符串) from table
//在字符串的头部截掉字符X(不能是字符串),有几个截掉几个,没有X则不截
SELECT TRIM(TRAILING 'S' FROM 'SSXXBUYGS')FROM DUAL
返回SSXXBUYG
trim(trailing 截取字符X from 字符串)from table
//在字符串的尾巴截掉字符X,有几个截掉几个,没有则不截
SELECT TRIM(BOTH 'S' FROM 'SSXXBUYGS')FROM DUAL
返回XXBUYG
trim(both 截取字符X from 字符串)from table
//在字符串的头部和尾巴分别截掉X,没有就不截
select trim(' XXBUYG ')from dual
trim(字符串) from table
//截掉字符串的头和尾巴的空格
2.replace(string,替换substring,被替换substring)
select replace('bhgfygggst','aaa','ggg') from dual
返回bhgfyaaast
3.rank()//排名函数(在order by的基础上显示名词)
①rank()//不连续,需后跟where筛选条件(手动分区)(1114467)——重复值对应排名跳跃
select rank() over(order by grades desc) where object='Oracle'
②dense_rank()//连续(指排名数字为连续的1223345)——重复值排名也重复
select dense_rank() over(order by grades desc) where object='Oracle'
③rank() over(partition by 科室 order by 成绩 desc) //分区但区内不连续
select rank() over(partition by 科室 order by 成绩 desc)from table
//安全(122256)专业知识(111446)企业文化(12244668)
④dense_rank() over(partition by 科室 order by 成绩 desc) //分区且区内连续
select dense_rank() over(partition by 科室 order by 成绩 desc)from table
//安全(112334) 专业知识(1223455)....
4.row_number()//排名(不考虑重复值,依次往下排)
select row_number() over(partition by 科室 order by 成绩 desc) from table
//安全(123456) 专业知识(123456)....
5.ListAGG(分组合并字段,'字段值间的分隔符')within group(order by 分组合并字段)from table group by 固定字段
select ListAGG(City',') within group(order by City) from table group by Country
//将同一Country的City合并到一行(一个值)