Oracle字符处理笔记

1、使用trim+translate函数判断字段是否是数字：
select * from table where trim(translate(column,’0123456789’,’ ‘)) is NULL;

2、使用regexp_like函数返回字段是数字的全部数据：
select * from table where regexp_like(column,’^[0-9]+[0-9]$’);
要注意的是：regexp_like函数不是在所有的oracle版本中都能使用的。
regexp_like是oracle支持正则表达式的四个函数：regexp_like，regexp_replace，regexp_instr,regexp_substr中的 一个，有关这方面更加详细信息，请关注相关文档。
eg. select regexp_replace(‘hjbfgcoqwue8723r8fhescb938r’,’[^0-9]’) from dual;
得到的结果就是：87238938；
ORACLE中的支持正则表达式的函数主要有下面四个：

• REGEXP_LIKE ：与LIKE的功能相似
• REGEXP_INSTR ：与INSTR的功能相似
• REGEXP_SUBSTR
• REGEXP_REPLACE ：与REPLACE的功能相似

它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，
但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。
POSIX 正则表达式由标准的元字符（metacharacters）所构成：
‘^’ 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
‘$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则$ 也匹
配 ‘\n’ 或 ‘\r’。
‘.’ 匹配除换行符之外的任何单字符。
‘?’ 匹配前面的子表达式零次或一次。
‘+’ 匹配前面的子表达式一次或多次。
‘*’ 匹配前面的子表达式零次或多次。
‘|’ 指明两项之间的一个选择。例子’^([a-z]+|[0-9]+)$’表示所有小写字母或数字组合成的 字符串。 select regexp_replace(‘hjbfgcoqwue8723aaAAAr8fhe我scXb938rz’,’[[:lower:]]|[[:upper:]]’ ) from dual; ‘( )’ 标记一个子表达式的开始和结束位置。 ‘[]’ 标记一个中括号表达式。 ‘{m,n}’ 一个精确地出现次数范围，m=<出现次数<=n，’{m}’表示出现m次，’{m,}’表示至少 出现m次。 \num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。 字符簇： [[:alpha:]] 任何字母。 [[:digit:]] 任何数字。 [[:alnum:]] 任何字母和数字。 [[:space:]] 任何白字符。 [[:upper:]] 任何大写字母。 [[:lower:]] 任何小写字母。 [[:punct:]] 任何标点符号。 [[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。一般用在图片编码检测上。 各种操作符的运算优先级 \转义符 (), (?:), (?=), [] 圆括号和方括号 *, +, ?, {n}, {n,}, {n,m} 限定符 ^,$, anymetacharacter 位置和顺序
|
*/

3、自动补全：
LPAD(id, 8, 0):给ID从前自动补0至8位字符；
RPAD(id, 8, 0):给ID从后自动补0至8位字符；
4、列转行：
WM_CONCAT（fieldname）
WMSYS.WM_CONCAT（fieldname）
配合group by使用。
eg:
SELECT training_dt,
WMSYS.WM_CONCAT(employee_ID)
FROM TraningTable
GROUP BY training_dt

5、取并集union，交集INTERSECT和差集MINUS:
eg：
差集MINUS:
获取两个表结构一样的数据的差集：
select * from table1 minus select * from table2
获得table1中存在但是table2中不存在的数据，会比对select中的每个字段的数据，会自动去重;
交集INTERSECT：获得两个表中数据完全一致的数据。
并集union：获得两个表中的所有数据合并，并去重。
并集union all:获得两个表中的所有数据合并，不去重。

6、取某行数据多个字段中的最大值greatest 和最小值least：
SELECT id, chinese, math, english,
greatest (chinese, math, english) max,
least(chinese, math, english) min
FROM tb;

7、Max(name) keep(dense_rank first/last order by id) over(partition by group_id)
以group id分组，获得id最大/最小的用户名。
Min(name) keep(dense_rank first/last order by id) over(partition by group_id)
以group id分组，获得id最小/最大的用户名。
first_values(name) order by id,group_id
获取id和group id升序下的第一个条数据的name数据。

8、ROW_NUMBER() over(order by DEPTID) as RNUM 根据部门无分组排序并标记rownum
partition by 用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一个反映统计值的记录。
ROW_NUMBER() over(partition by deptid ORDER BY salary desc) as RNUM 以部门为分组，根据工资降序排序并标记row num。
注：在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where group by order by 的执行。
rank() over()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）。
rank() over(partition by deptid order by salary)以部门为分组，根据工资升序排序并标记排名。此排名为部门内排名，即每个部门都有1、2、3。。。标记。
dense_rank() over()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。dense_rank() over(partition by deptid order by salary) 以部门为分组，根据工资升序排序并标记排名。此排名为部门内排名，即每个部门都有1、2、3。。。标记。
For example:
使用ROW_NUMBER删除重复数据
—假设表TAB中有a,b,c三列，可以使用下列语句删除a,b,c都相同的重复行。
DELETE FROM (select year,QUARTER,RESULTS,row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO FROM SALE ) WHERE ROW_NO>1

9、ceil（n）向上取整，即取大于等于数值n的最小整数；
floor（n）向下取整，即取小于等于数值n的最大整数；
TRUNC（number[,decimals]）截取小数点后decimals位取值；
TRUNC（date,[fmt]）截取日期；
trunc(sysdate,’yyyy’) –返回当年第一天.
trunc(sysdate,’mm’) –返回当月第一天.
trunc(sysdate,’d’) –返回当前星期的第一天.
ROUND( number, [ decimal_places ] )：小数点后decimals位四舍五入取值。