如何减少由子字符串和instring引起的代码重复? [英] How to reduce code duplication caused by substring and instring?
问题描述
我需要将一个以'\ n'分隔的文本字段(描述)解析为三个单独的字段.我通过利用substr
和instr
来做到这一点,但是它导致难以阅读和重复的sql.有没有一种方法可以创建和使用变量或表达式来保存instring函数返回的位置"值,因此我可以将该变量传递给substr
?我在下面发布的代码可以正常工作并返回正确的结果,但是感觉不正确.有很多重复.
I need to parse a text field (Description), delimited by '\n', into three separate fields. I am doing this by utilizing substr
and instr
, but it results in difficult to read and repetitive sql. Is there a way to create and use a variable or expression to hold the "position" value returned by the instring function so I can pass that variable to substr
instead? My code posted below functions and returns the correct results, but it doesn't feel right. There's a lot of duplication.
相关原始数据:
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\nDescription:Planogram: 60E90001006.0SMA :: UPC: is not numeric\nSeverity:0
40846810 2013-11-18 11:04:11.0000000 abc$userid The following Message List entry has been logged:\nError Number:5000\nDescription:Planogram: 60E90001006.0SMA :: ID: NEW not 9 digits\nSeverity:0
40846811 2013-11-18 11:04:11.0000000 abc$userid The following Message List entry has been logged:\nError Number:5001\nDescription:Planogram: 60E90001006.0SMA :: ID: NEW not numeric\nSeverity:0
所需结果:
DBKEY DBTIME USERID ERROR_NUM DESCRIPTION SEVERITY
40846809 2013-11-18 11:04:11.0000000 userid 5011 Planogram: 60E90001006.0SMA :: UPC: is not numeric 0
40846810 2013-11-18 11:04:11.0000000 userid 5000 Planogram: 60E90001006.0SMA :: ID: NEW not 9 digits 0
40846811 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
当前代码:
SELECT DBKEY,DBTIME,
SUBSTR(DBUSER,INSTR(DBUSER,'$',1,1)+1) AS USERID,
SUBSTR(ERROR_NUM,INSTR(ERROR_NUM,':')+1) AS ERROR_NUM,
SUBSTR(DESC1,INSTR(DESC1,':')+1) AS DESCRIPTION,
SUBSTR(SEVERITY,INSTR(SEVERITY,':')+1) AS SEVERITY
FROM(
SELECT l.DBKEY,DBTIME,DBUSER,
--substring(description,first+2,second-first-2)
SUBSTR(DESCRIPTION,INSTR(DESCRIPTION,'\n',1,1)+2,INSTR(DESCRIPTION,'\n',1,2)-INSTR(DESCRIPTION,'\n',1,1)-2) AS ERROR_NUM,
--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,
--substring(description,third+2)
SUBSTR(DESCRIPTION,INSTR(DESCRIPTION,'\n',1,3)+2) AS SEVERITY
/*,
INSTR(DESCRIPTION,''\n'',1,1) as first,
INSTR(DESCRIPTION,''\n'',1,2) as second,
INSTR(DESCRIPTION,''\n'',1,3) as third,
*/
FROM EVENT_LOG l
)derivedtbl
推荐答案
我建议使用REGEXP_SUBSTR
,它是实现预期结果的好功能:
I suggest using REGEXP_SUBSTR
which is a great function to achieve your expected results:
SELECT
l.DBKEY,
l.DBTIME,
REGEXP_SUBSTR(l.DBUSER, '[^$]+$') AS USERID,
REGEXP_SUBSTR(l.DESCRIPTION, '[0-9]{4}') AS ERROR_NUM,
REPLACE(REGEXP_SUBSTR(l.DESCRIPTION, 'Planogram:[^\]+\\n'), '\n', '')
AS DESCRIPTION,
REGEXP_SUBSTR(l.DESCRIPTION, '\d+$') AS SEVERITY
FROM
EVENT_LOG l;
我已经创建并测试了 SQLFiddle .有关REGEXP_SUBSTR
的更多信息,您可能需要阅读 Oracle Docs .
I have created and tested a SQLFiddle. For more information about REGEXP_SUBSTR
you may want to read the Oracle Docs.
这篇关于如何减少由子字符串和instring引起的代码重复?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!