1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
|
SQL> r
1 WITH t AS (SELECT '10jfjfhrt' a FROM dual
2 union ALL SELECT '5adsae' FROM dual
3 union ALL SELECT '14asa' FROM dual
4 union ALL SELECT '12222' FROM dual
5 union ALL SELECT '' FROM dual
6 union ALL SELECT '.' FROM dual
7 union ALL SELECT 'a' FROM dual)
8 Select a from (
9 Select
10 case when instr(substr(a,1,2),'.') = 0 and nvl(replace(translate(substr(a,1,2),'0123456789','000000000'),'0',''),'.') = '.'
11 then to_number(substr(a,1,2))
12 when SubStr(a,1,1) <> '.' and nvl(replace(translate(substr(a,1,1),'0123456789','000000000'),'0',''),'.') = '.'
13 then to_number(substr(a,1,1))
14 end nr,
15 a
16 from t)
17 where nr between 1 and 13
18*
A
---------
10jfjfhrt
5adsae
12222 |
Partager