【SQL】10g字符串处理函数regexp_substr的用法示例

在我们生产系统里，有这么一个配置表，他记录的工单派单的接收人员的排班表。

程序会读取这个表，然后将工作任务单派发给当班的那些人员（工号），表配置基本如下：

SQL> select * from v1;
DT1   STR1
-------- ---------------------------------------
20151001 SCKF100,CDKF200,MSKF300,ZYKF400,GYKF500
20151002 SCKF101,CDKF201,MSKF301,ZYKF401,GYKF501
20151003 SCKF102,CDKF202,MSKF302,ZYKF402,GYKF502
20151004 SCKF103,CDKF203,MSKF303
20151005 SCKF104,CDKF204,MSKF304,ZYKF404,GYKF504
20151006 GYKF505
20151007 SCKF106,CDKF206,ZYKF406,GYKF506
20151008 SCKF107,CDKF207,MSKF307,ZYKF407,GYKF507
20151009 SCKF108,CDKF208,MSKF308,ZYKF408,GYKF508
20151010 SCKF109,CDKF209,MSKF309,ZYKF409,GYKF509
20151011 ZYKF410,GYKF510
20151012 SCKF111,CDKF211,MSKF311,ZYKF411,GYKF511
20151013 SCKF112,CDKF212,MSKF312,ZYKF412,GYKF512
20151014 GYKF513
20151015 SCKF114,CDKF214,MSKF314,ZYKF414,GYKF514
20151016 ZYKF415,GYKF515
20151017 SCKF116,CDKF216,MSKF316,ZYKF416,GYKF516
20151018 SCKF117,CDKF217,MSKF317,ZYKF417,GYKF517
20151019 MSKF318,ZYKF418,GYKF518
20151020 CDKF219,MSKF319,ZYKF419,GYKF519
20 rows selected


SQL> 

DT1表示日期，STR1表示DT1所在当天配置的接收任务单的工号。

很明显，程序可以根据dt1,读取到str1，然后在应用中间件去分解这个串，得到各个工号。

但其实也可以在数据库内部进行这个分解，比如我们需要分解dt1=‘20151001’这天的工号列表：

SQL> select * from v1 where dt1='20151001';
DT1   STR1
-------- ---------------------------------------
20151001 SCKF100,CDKF200,MSKF300,ZYKF400,GYKF500


SQL> 

SQL> SELECT regexp_substr(str1, '[^,]+', 1, level) as newstr1
  2    FROM (SELECT str1 FROM v1 where dt1 = '20151001') t1
  3  connect by level <= length(translate(str1, ',' || str1, ',')) + 1;
NEWSTR1
------------------------------------------------------------------------------
SCKF100
CDKF200
MSKF300
ZYKF400
GYKF500

SQL>