干货 | Oracle 中 REGEXP_SUBSTR函数搜索格式化字段的值(横向竖向显示所有值)

一、需求分析

最近碰到一个需求，业务系统某个表的某个字段值（比如1,2,3,4,5,6,7,8,9,10）比较特殊，里面包含了以固定字符（比如逗号","）分割的值，业务系统希望格式化查询这个字段的值。

二、解决方案

在oracle里面搜索可以使用正则表达式函数，Oracle中支持正则表达式的函数主要有下面四个：

REGEXP_LIKE：与LIKE的功能相似
REGEXP_INSTR：与INSTR的功能相似
REGEXP_SUBSTR：与SUBSTR的功能相似
REGEXP_REPLACE：与REPLACE的功能相似
它们在用法上与Oracle函数LIKE、INSTR、SUBSTR和REPLACE用法相同，但是它们使用POSIX正则表达式代替了老的百分号（%）和通配符（_）字符。

三、REGEXP_SUBSTR函数使用说明

REGEXP_SUBSTR语法图

REGEXP_SUBSTR插图描述：

REGEXP_SUBSTR(source_char, pattern
              [, position
                 [, occurrence
                    [, match_param
                       [, subexpr
                       ]
                    ]
                 ]
              ]
             )

四、使用案例

创建测试表
create table xxx (c1 varchar2(100));
select * from xxx;

插入测试数据
insert into xxx values('1,2,3,4,5,6,7,8,9,10');

SQL> --格式化查询数据
SQL> select c1 from xxx;
C1

1,2,3,4,5,6,7,8,9,10

SQL> --格式化查询数据-,分割第1个个值
SQL> SELECT REGEXP_SUBSTR(c1,'[^,]+',1,1,'i') AS STR FROM xxx;
STR

SQL> --格式化查询数据-,分割第2个个值
SQL> SELECT REGEXP_SUBSTR(c1,'[^,]+',1,2,'i') AS STR FROM xxx;
STR

2
SQL> --格式化查询数据-,分割第3个个值
SQL> SELECT REGEXP_SUBSTR(c1,'[^,]+',1,3,'i') AS STR FROM xxx;
STR

3
SQL> --格式化查询数据-,分割第3个个值,横向显示
SQL> SELECT REGEXP_SUBSTR(c1,'[^,]+',1,1,'i') v1,REGEXP_SUBSTR(c1,'[^,]+',1,2,'i') v2,REGEXP_SUBSTR(c1,'[^,]+',1,3,'i') v3 FROM xxx;
V1 V2 V3

1 2 3
SQL> --格式化查询数据-,分割所有值，竖向显示，使用connect函数
SQL> SELECT REGEXP_SUBSTR(c1, '[^,]+', 1, LEVEL, 'i') AS STR
2 FROM xxx
3 CONNECT BY LEVEL <= LENGTH(c1) - LENGTH(REGEXP_REPLACE(c1, ',', ''))+1;
STR

1
2
3
4
5
6
7
8
9
10
10 rows selected

SQL>

| 北京 | 上海 | 广州 | 成都 |

4008-906-960