[indent]REGEXP_SUBSTR函数格式如下：[/indent][indent]function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr ：需要进行正则处理的字符串
__pattern ：进行匹配的正则表达式
__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）
__occurrence ：标识第几个匹配组，默认为1
__modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）[/indent][font=Georgia][size=14px]实际应用如下：在oracle中，使用一条语句实现将'34,56,-23'拆分成'34'，'56'，'-23'的集合。
[size=14px]

[size=14px]SELECT a.*,REGEXP_SUBSTR(BELONG_DOMAINS ,'[^,]+',1,l) AS ab
[size=14px]

[size=14px] FROM LIST_EXPERT a,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b [size=14px] [size=14px]WHERE l <=LENGTH(BELONG_DOMAINS) - LENGTH(REPLACE(BELONG_DOMAINS,','))+1 [size=14px] [size=14px]ORDER BY 1,2; [size=14px] [size=14px] [size=14px] [color=#b54141]wm_concat函数 [font=Verdana, Tahoma, Arial, sans-serif][size=13px][font=Georgia]首先让我们来看看这个神奇的函数[color=#b54141]wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用[font=Georgia]准备测试数据 [font=Georgia]SQL> create [color=#b54141]table test(id number,name varchar2(20));[font=Georgia]SQL> insert into test values(1,'a');[font=Georgia]SQL> insert into test values(1,'b');[font=Georgia]SQL> insert into test values(1,'c');[font=Georgia]SQL> insert into test values(2,'d');[font=Georgia]SQL> insert into test values(2,'e');
[font=Georgia]SQL> commit;
[font=Georgia]效果1 : 行转列
[font=Georgia]SQL> select wm_concat(name) from test;[font=Georgia]WM_CONCAT(NAME)[font=Georgia]-------------------------------------------------------------------------[font=Georgia]a,b,c,d,e
[font=Georgia]效果2: 把结果里的逗号替换成"|"
[font=Georgia]SQL> select replace(wm_concat(name),',','|') from test;[font=Georgia]REPLACE(WM_CONCAT(NAME),',','|')[font=Georgia]-----------------------------------------------------------------------[font=Georgia]a|b|c|d|e

[font=Georgia]效果3:按ID分组合并name
[font=Georgia]SQL> select id,wm_concat(name) name from test group by id;[font=Georgia]ID NAME[font=Georgia]---------- ------------------------------[font=Georgia]1 a,b,c[font=Georgia]2 d,e
[font=Georgia]懒人扩展用法:[font=Georgia]案例:我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单
[font=Georgia]SQL> select 'create or replace view as select '|| wm_concat(column_name) || ' from dept'from user_tab_columns where table_name='DEPT';[font=Georgia][size=1]'CREATEORREPLACEVIEWASSELECT'||WM_CONCAT(COLUMN_NAME)||'FROMDEPT'