1.遍历字符串

--level:伪列 和connect by 结合使用

select level from dual connect by level <= 5

得到结果

level

1

2

3

4

5

connect by是树形查询的一个子句，后面的level是一个伪列，用来表示树形查询的级次关系，通过level <= 5循环5次，达到上述效果。level也可以作为定位标识使用

eg 需要对字符串 “天天向上” 和“TTXS”进行循环，达到下述效果

创建视图：create or replace view v

as

select '天天向上' as 汉字,'TTXS' as 首拼 from dual;

需要对字符串 “天天向上” 和“TTXS”进行循环，达到下述效果

天 T

天 T

向 X

上 S

思路：(1).通过level加上定位标识

select v.汉字,v.首拼,level from v connect by level <= length(v.汉字);

(2).每一行取对应位置的字符，通过substr截取字符串 substr(字段名,开始位置,截取长度) eg:substr(name,2,3):从name第2个字段开始截取3个长度

select substr(v.汉字,level,1) as 汉字拆分,substr(v.首拼,level,1) as 首拼拆分 from v connect by level <= length(v.汉字);

2.字符串文字中包含 '

在写sql时字符串内的'处理方式，其实只要写两个 ''就可以

eg:select 'a''b''b' from dual

查询结果： a'b'b

3.计算字符在字符串中出现的次数

使用regexp_count 函数

eg:

创建视图：create or replace view v

as

select 'clerk,king,miller' as str from dual;

查询,的数量

select regexp_count(str,',') from v;

4.从字符串中删除不需要的字符

(1)

(2)

(3)regexp_replace(字段名,'[需要被替代的值]')

5.将字符和数字数据分离

eg:销售订单单据号是字母和数字混合的字符串，用下面的写法可以把字母和数字分离

用正则表达式：[0-9] 和 [^0-9] 来进行筛选

select regexp_replace(vbillcode,'[0-9]','') shuzi,

regexp_replace(vbillcode,'[^0-9]','') so

from so_saleorder

6.查询只含字母或数据型的数据

在一个字段的值中，有些数据为数字，有些数据为字母，有些数据既包含数字，又包含字母，甚至包含一些特殊符号，比如$等

现在要求返回只包含数字和字母的值。

方案1：直接用正则表达式 ^[0-9a-zA-Z]+$

创建以下视图作为案例

create or replace view v as

select '123' as data from dual union all

select 'abc' from dual union all

select '123abc' from dual union all

select 'abc1' from dual union all

select '%$a1b3c' from dual union all

select 'a 2' from dual;

eg:select data from v where regexp_like(data,'^[0-9a-zA-Z]+$')

首先和前面一样，regexp_like对应普通的like

regexp_like(data,'[ABC]')就相当于(like '%A%' or like '%B%' or like '%C%')

regexp_like(data,^[0-9a-zA-Z])就相当于(like '%数字%' or like '%小写字母%' or like '%大写字母%')

注意；是前后都有%的模糊查询

"^"不在方括号表示字符串的开始，这里还有一个$,在方括号外面，表示字符串的结束。

另一个概念是'+' 和 '*','*' 表示匹配前面的子表达式零次或多次

创建以下视图作为案例

create or replace view v as

select '167' as str from dual union all

select '1234567' as str from dual

regexp_like(str,'16+'),'+'前的子表达式是 6，至少匹配6一次，也就相当于(like '16%' or like '166%' or ...) 等价于 like '16%'

regexp_like(str,'16*'),'*'前的表达式是6，至少匹配6零次，相当于(like '1%' or like '16%' or ...) 等价于like '1%'.

select * from v where regexp_like(str,'16+')

select * from v where regexp_like(str,'16*')

7.提取姓名的大写首字母缩写

要求返回下面view中的大写字母，中间加'.'，显示为'H.M'

create or replace view v as

select 'Michael Hartstein' as al from dual

方案1：利用regexp_replace的分组替换功能

select regexp_replace(v.al,'([[:upper:]])(.*)([[:upper:]])(.*)','\1.\3') as sx from v

括号()将子表达式分组为一个替换单元、量词单元或后向引用单元。

在这个查询中，我们用()把字符串分成了四个组，其中第1、3组是大写字母，然后通用后向引用(\1.\3')就分别取到了两个组的大写字母，并在中间增加了字符"."

8.按字符串中的数值排序

建立如下view，要求按其中的数字排序

方案1：把其中的数字取出来，作为一列，进行排序

方案2：用translate函数，直接替换掉非数字字符，进行排序

9.根据表中的行创建一个分隔列表

eg:工资表按照部门求和，需要体现出这个部门中的人名

方案1：用listagg分析函数

select

deptno,

sum(sal) as total_sal,

listagg(ename,',') within group(order by ename) as total_ename

from emp

group by deptno;

和sum一样，listagg在这里起汇总的作用。sum的作用是把数值的结果加到一起，而listagg是把字符串结果连在一起。

10.提取第n个分隔的子串

建立如下视图

create or replace view v as

select listagg

11.分解IP地址

eg:把192.168.1.131从.进行分隔

方案1：用regexp_substr()按照.进行分割，分别取1、2、3、4

select regexp_substr(v.ip,'[^.]+',1,1) a,

regexp_substr(v.ip,'[^.]+',1,2) b,

regexp_substr(v.ip,'[^.]+',1,3) c,

regexp_substr(v.ip,'[^.]+',1,4) d

from (select '192.168.1.131' as ip from dual) v

12.将分隔数据转换为多值IN列表

假设前端传入了一个字符串列表(如：CLARK,KING,MILLER),要求根据这个串查询数据

直接带入肯定不能查询，需要进行转换，这就是正则表达式的优势

建立如下视图：

create or replace view v as

select 'CLARK,KING,MILLER' as emps from dual;

查询伪列以及正则表达式

select regexp_substr(v.emps,'[^,]+',1,level) as ename,

level,

'regexp_substr('''||v.emps || ''',''[^,]+'',1,'|| to_char(level)||')' as reg,

(length(translate(v.emps,','||v.emps,','))+1) as a

from v

connect by level <= (length(translate(v.emps,','||v.emps,','))+1)

13.按字母顺序排列字符串

将emp.name中的内容按字母顺序重新排序，如：ADAMS>AADMS.对于这种问题

(1).将name拆分未单个字母显示

(2).把多行数据显示为一行

先以一行数据为例：

用substr()进行分割，用level伪列进行循环标记

var v_ename varchar2(50);

exec :v_ename := 'ADAMS';

select :v_ename as ename,substr(:v_ename,level,1) as c from dual

connect by level <= length(:v_ename) ;

select 'ADAMS' as ename,substr('ADAMS',level,1) as c from dual

connect by level <= length('ADAMS') ;

用listagg()进行拼接

select 'ADAMS' as ename,listagg(substr('ADAMS',level,1)) within group(order by substr('ADAMS',level,1))

as c from dual

connect by level <= length('ADAMS') ;

以销售订单为例：把销售订单的订单号进行重新排序

select vbillcode,

(select listagg(substr(vbillcode,level,1)) within group(order by substr(vbillcode,level,1)) from dual

connect by level <= length(vbillcode)) as new_vbillcode

from so_saleorder

如果需要对重新排列的结果进行去重，如何操作？

在标量子查询中加一个group by即可

select vbillcode,

(select listagg(min(substr(vbillcode,level,1))) within group (order by min(substr(vbillcode,level,1))) from dual

connect by level <= length(vbillcode) group by substr(vbillcode,level,1)) as new_vbillcode from so_saleorder

Oracle学习日记——给查询结果排序

Oracle学习日记——单表查询