①字符截取

--截取'12345678:2345'中冒号之前的数据
select substr('12345678:2345', 1, instr('12345678:2345', ':') - 1) from dual;

--截取'12345678:2345'中冒号之前的数据

select substr('12345678:2345', instr('12345678:2345', ':') + 1, length('12345678:2345') - 2) from dual;

--截取第一个冒号之后第二个冒号之前的数据

SELECT SUBSTR('12345678:236:45',

INSTR('12345678:236:45', ':') + 1,

INSTR('12345678:236:45', ':', 1, 2) - INSTR('12345678:236:45', ':', 1, 1) - 1) FROM DUAL;

--从任意字符串中提取数据

SELECT to_number(translate('a1b2c/*c:98d', '1' || translate('a1b2c/*c:98d', '1234567890.A', 'A'), '1')) FROM dual;