1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| SQL> WITH MaTable (DATE_FIL, TAILLE_FIC) AS
2 (
3 SELECT date '2013-01-01', '1.2M' FROM dual union ALL
4 SELECT date '2013-01-11', '526K' FROM dual union ALL
5 SELECT date '2013-03-18', '2.3 Go' FROM dual
6 )
7 SELECT DATE_FIL
8 , TAILLE_FIC
9 , to_number(trim(translate(TAILLE_FIC, translate(TAILLE_FIC, '.0123456789', ' '), ' ')),'9999999999D9999999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')
10 * case substr(trim(translate(TAILLE_FIC, '.0123456789', ' ')), 1, 1)
11 when 'K' then power(1024, 1)
12 when 'M' then power(1024, 2)
13 when 'G' then power(1024, 3)
14 else 1
15 end AS taille_fic_nm
16 FROM MaTable;
DATE_FIL TAILLE TAILLE_FIC_NM
-------- ------ -------------
01/01/13 1.2M 1258291,2
11/01/13 526K 538624
18/03/13 2.3 Go 2469606195
SQL> |
Partager