1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| WITH got_my_data AS
(SELECT 'ABC101' step, 'texte' txt FROM dual UNION ALL
SELECT 'DE102' , 'texte' FROM dual UNION ALL
SELECT 'F101bis' , 'texte' FROM dual UNION ALL
SELECT 'GH100' , 'texte' FROM dual UNION ALL
SELECT 'DJ050' , 'texte' FROM dual UNION ALL
SELECT 'ZE100bis' , 'texte' FROM dual
)
SELECT step, txt, TO_NUMBER(TRANSLATE(step, '0'|| TRANSLATE(step, 'a0123456789', 'a'), '0')) num
FROM got_my_data
WHERE TO_NUMBER(TRANSLATE(step, '0'|| TRANSLATE(step, 'a0123456789', 'a'), '0')) > 100
STEP TXT NUM
ABC101 texte 101
DE102 texte 102
F101bis texte 101 |
Partager