如何减少由子字符串和instring引起的代码重复？

恒玮时装衣架货架有限公司

我需要将一个以’\
n’分隔的文本字段（描述）解析为三个单独的字段。我通过利用substr和来执行此操作instr，但是它导致难以阅读和重复的sql。有没有一种方法可以创建和使用变量或表达式来保存instring函数返回的“位置”值，因此我可以将该变量传递给它substr？我在下面发布的代码可以正常工作并返回正确的结果，但是感觉不正确。有很多重复。
相关原始数据：
DBKEY       DBTIME                      DBUSER     DESCRIPTION
40846809    2013-11-18 11:04:11.0000000 abc$userid  The following Message List entry has been logged:\nError Number:5011\nDescriptionlanogram: 60E90001006.0SMA :: UPC:  is not numeric\nSeverity:0
; O7 U& n: U1 t, G( x% Z40846810    2013-11-18 11:04:11.0000000 abc$userid  The following Message List entry has been logged:\nError Number:5000\nDescriptionlanogram: 60E90001006.0SMA :: ID: NEW  not 9 digits\nSeverity:0
9 Y, m; W! Y# F9 n2 @6 l' `40846811    2013-11-18 11:04:11.0000000 abc$userid  The following Message List entry has been logged:\nError Number:5001\nDescriptionlanogram: 60E90001006.0SMA :: ID: NEW  not numeric\nSeverity:0
  F9 g6 p5 W$ G! o所需结果：
5 B8 F3 p0 y, ]; YDBKEY       DBTIME                      USERID  ERROR_NUM   DESCRIPTION SEVERITY
40846809    2013-11-18 11:04:11.0000000 userid  5011        Planogram: 60E90001006.0SMA :: UPC:  is not numeric 0
9 Q; s8 g" ^+ J; M0 `" G9 Q40846810    2013-11-18 11:04:11.0000000 userid  5000        Planogram: 60E90001006.0SMA :: ID: NEW  not 9 digits    0
3 w" a$ {: Q) H& a40846811    2013-11-18 11:04:11.0000000 userid  5001        Planogram: 60E90001006.0SMA :: ID: NEW  not numeric 0
40846812    2013-11-18 11:04:11.0000000 userid  5003        Planogram: 60E90001006.0SMA :: ID: NEW  ID must begin with 000,200,220,900,950,990,or 999   0
8 B3 K$ P9 U0 j1 w5 x; C+ d$ P当前代码：
+ [' |4 C8 H1 k/ l/ y7 SSELECT DBKEY,DBTIME,
3 j, R: ?4 y$ K& a        SUBSTR(DBUSER,INSTR(DBUSER,'$',1,1)+1) AS USERID,
0 }/ W9 U" m- F/ k3 u& C        SUBSTR(ERROR_NUM,INSTR(ERROR_NUM,':')+1) AS ERROR_NUM,
        SUBSTR(DESC1,INSTR(DESC1,':')+1) AS DESCRIPTION,
, ]8 r" Z2 w' ~  R2 N- o. q5 \        SUBSTR(SEVERITY,INSTR(SEVERITY,':')+1) AS SEVERITY
FROM(
    SELECT l.DBKEY,DBTIME,DBUSER,
  B; B* j4 j6 V- H6 ]        --substring(description,first+2,second-first-2)
/ a$ n  w* {- d( }. {        SUBSTR(DESCRIPTION,INSTR(DESCRIPTION,'\n',1,1)+2,INSTR(DESCRIPTION,'\n',1,2)-INSTR(DESCRIPTION,'\n',1,1)-2) AS ERROR_NUM,
! d( u" U( P8 ~6 ]9 ^        --substring(description,second+2,third-second-2)
        SUBSTR(DESCRIPTION,INSTR(DESCRIPTION,'\n',1,2)+2,INSTR(DESCRIPTION,'\n',1,3)-INSTR(DESCRIPTION,'\n',1,2)-2) AS DESC1,
5 _4 V- V: B# j# J( h, P2 l        --substring(description,third+2)
' U6 B8 N4 p( T" ~) F% E        SUBSTR(DESCRIPTION,INSTR(DESCRIPTION,'\n',1,3)+2) AS SEVERITY
        /*,
        INSTR(DESCRIPTION,''\n'',1,1) as first,
+ r7 z2 d/ N6 t2 Z2 s! o; {5 A5 u        INSTR(DESCRIPTION,''\n'',1,2) as second,
4 ~4 S1 q% c# U; O        INSTR(DESCRIPTION,''\n'',1,3) as third,
- H' _! L5 k8 ]' I/ h        */
2 ^# ]4 \( o! z    FROM EVENT_LOG l
)derivedtbl
               
解决方案:
               
- E! r( n$ g" X$ a" d
% ]5 v# d0 p$ ~5 u9 R( z
                我建议使用REGEXP_SUBSTR哪一个功能可以达到预期的效果：
4 Z! J, Q- V  jSELECT
    l.DBKEY,
2 v7 d  B* [; S1 c, v# @    l.DBTIME,
5 s5 r. [- ~& w1 ~2 }7 b; Z    REGEXP_SUBSTR(l.DBUSER, '[^$]+$')                 AS USERID,
! I1 D) ?+ |+ s6 x9 O    REGEXP_SUBSTR(l.DESCRIPTION, '[0-9]{4}')          AS ERROR_NUM,
5 R; E0 |) h3 n" ^    REPLACE(REGEXP_SUBSTR(l.DESCRIPTION, 'Planogram:[^\]+\\n'), '\n', '')
                                                      AS DESCRIPTION,
    REGEXP_SUBSTR(l.DESCRIPTION, '\d+$')              AS SEVERITY
4 A. q+ [6 z3 T/ d1 [4 |( hFROM
    EVENT_LOG l;
" q2 L# \5 F# C7 B/ e2 f我已经创建并测试了SQLFiddle。有关更多信息，REGEXP_SUBSTR您可能需要阅读Oracle
3 k+ E1 T, n. [, X% V/ f0 N2 {Docs。