我在 Oracle 数据库中有一个列,它有类似这样的数据
column1
/opt/log/data/abcd.efghi.jklmn.aaa.txt
/opt/log/data/abbbcd.efccghi.jkdsdflmn.abab.txt
/opt/log/data/nmvcnmcd.efjhjghi.jkvslmn.abcbc.txt
/opt/log/data/hjsdhj.hjfdhdf.hdfhjd.aghag.txt
/opt/log/data/dfhjfdhj.yureyer.qwtyq.hjahjh.txt
我想以这样的方式拆分数据
**firstdot seconddot thirdnfourthdot**
abcd efghi jklmn.aaa
abbbcd efccghi jkdsdflmn.abab
nmvcnmcd efjhjghi jkvslmn.abcbc
hjsdhj hjfdhdf hdfhjd.aghag
dfhjfdhj yureyer qwtyq.hjahjh
我可以通过
select substr(column1,instr(column1,'.',1+1,instr(column1,'.',1,2)-instr(column1,'.',1,1)-1) as secondot
但我无法得到其余的。你们能帮忙吗。
非常感谢
如果没有正则表达式,您需要为您需要的每个子字符串回复相同的逻辑,每个 timi 都根据该子字符串的“终止符”的位置选择初始位置和长度。
/* input data */
with yourTable(column1) as (
select '/opt/log/data/abcd.efghi.jklmn.aaa.txt' from dual union all
select '/opt/log/data/abbbcd.efccghi.jkdsdflmn.abab.txt' from dual union all
select '/opt/log/data/nmvcnmcd.efjhjghi.jkvslmn.abcbc.txt' from dual union all
select '/opt/log/data/hjsdhj.hjfdhdf.hdfhjd.aghag.txt' from dual union all
select '/opt/log/data/dfhjfdhj.yureyer.qwtyq.hjahjh.txt' from dual
)
/* query */
select substr(column1, instr(column1, '/', -1) +1, instr(column1, '.') - instr(column1, '/', -1)-1) firstDot,
substr(column1, instr(column1, '.') +1, instr(column1, '.', 1, 2) - instr(column1, '.') -1) secondDot,
substr(column1, instr(column1, '.', 1, 2) +1, instr(column1, '.', 1, 4) - instr(column1, '.', 1, 2) -1) thirdAndFourthDot
from yourTable
给出:
FIRSTDOT SECONDDOT THIRDANDFOURTHD
--------------- --------------- ---------------
abcd efghi jklmn.aaa
abbbcd efccghi jkdsdflmn.abab
nmvcnmcd efjhjghi jkvslmn.abcbc
hjsdhj hjfdhdf hdfhjd.aghag
dfhjfdhj yureyer qwtyq.hjahjh
以更易读的方式:
select substr(column1, lastSlashPos +1, firstDotPos - lastSlashPos -1) as firstDot,
substr(column1, firstDotPos +1, secondDotPos - firstDotPos -1) as secondDot,
substr(column1, secondDotPos +1, fourthDotPos - secondDotPos -1) as thirdAndFourthDot
from (
select instr(column1, '/', -1) as lastSlashPos,
instr(column1, '.') as firstDotPos,
instr(column1, '.', 1, 2) as secondDotPos,
instr(column1, '.', 1, 3) as thirdDotPos,
instr(column1, '.', 1, 4) as fourthDotPos,
column1
from yourTable
)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句