oracle 字符格式化,干货 | 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

1

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

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

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

1234567891010 rows selected

SQL>

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

4008-906-960