场景描述:有一个字段为端口描述,以下划线作为分隔符拼接了多个信息,需要将四个信息分别提取出
分析:使用INSTR、SUBSTR函数
完成展示:
SELECT SUBSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX',
1,
INSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX', '_', 1, 1) - 1),
SUBSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX',
INSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX', '_', 1, 1) + 1,
(INSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX', '_', 1, 2) -
INSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX', '_', 1, 1) - 1)),
SUBSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX',
INSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX', '_', 1, 2) + 1,
(INSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX', '_', 1, 3) -
INSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX', '_', 1, 2) - 1)),
SUBSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX',
(INSTR('XXX_XXXXXXXX_XXXM_XXXXXXXXXXXX', '_', 1, 3) +1))
FROM DUAL;