oracle 以分隔号取内容,Oracle数据库如何按照特定分隔符切割字段
with ttt as(select '641177' pk_id,'886.085.6+886.085.9+886.085.10+886.085.11-886.085.14' right_resultfrom dualUNION ALLSELECT '641178', '886.086.6+886.086.9+886.086.10+886.086.11-886.086.14'FROM DUALU
with ttt as
(select '641177' pk_id,
'886.085.6+886.085.9+886.085.10+886.085.11-886.085.14' right_result
from dual
UNION ALL
SELECT '641178', '886.086.6+886.086.9+886.086.10+886.086.11-886.086.14'
FROM DUAL
UNION ALL
SELECT '265449',
'736.1.1 + 736.4.1 + 736.7.1 - 736.8.1 + 736.9.1 + 736.10.1'
FROM DUAL)
SELECT PK_ID,
RIGHT_RESULT,
V_ITEM,
SUBSTR(RIGHT_RESULT, INSTR(RIGHT_RESULT, V_ITEM) + LENGTH(V_ITEM), 1) V_CALULATOR
FROM (select pk_id,
right_result,
REGEXP_SUBSTR(replace(replace(replace(replace(right_result,
'+',
','),
'-',
','),
'*',
','),
'/',
','),
'[^,]+',
1,
LEVEL) V_ITEM
from ttt
CONNECT BY LEVEL < REGEXP_COUNT(replace(replace(replace(replace(right_result,
'+',
','),
'-',
','),
'*',
','),
'/',
','),
',') + 2
AND PRIOR PK_ID = PK_ID
AND PRIOR DBMS_RANDOM.value IS NOT NULL)
更多推荐
所有评论(0)