Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
1 juillet 2013 1 01 /07 /juillet /2013 10:22

De retour sur Oracle après 3 ans dans le monde du pipo, je me refais un peu la main en participant au forum OTN.

 

Et là, sur une question classique "comment vérifier qu'une chaîne de caractères est un nombre sous Oracle ?", je m'aperçois que les intervenants d'OTN à priori chevronnés sont en fait un peu à la masse par rapport aux amis de developpez.net d'il y a 3-5 ans.

 

Notamment, pour parser une chaîne potentiellement numérique, il ne faut pas simplement vérifier qu'elle est composée de chiffres !

 

Je m'étais amusé à l'époque sur ce même non-blog à faire un is_numeric imbuvable : 

http://pacmann.over-blog.com/article-yet-another-oracle-isnumeric-function-51158452.html

 

Je profite de l'occasion pour enfin compléter quelques points : 

- Quite à simplement vérifier la présence de chiffres uniquement la fonction rtrim fait très bien le boulot, pas besoin de translate + trim !

  => Un grand merci à McM de developpez.net

- Faire une vraie regexp bien touffue, c'est idéal

  => Un grand merci à Bloon de developpez.net

 

SQL> SELECT to_number(c)

  2      , case when regexp_like (c,'^(-|\+)?(\d*|\d+,|,\d+|\d+,\d+)(E(-|\+)?\d+)?$','i')then 1 else 0 end win

  3      , case when rtrim(c, '1234567890') is null then 1 else 0 end mcm

  4      , case when regexp_like (c, '^[[:digit:]]$') then 1 else 0 end loosexp

  5  FROM (

  6    SELECT '1' c FROM DUAL UNION ALL

  7    SELECT '+2'FROM DUAL UNION ALL

  8    SELECT '3,4' FROM DUAL UNION ALL

  9    SELECT '-5' FROM DUAL UNION ALL

 10    SELECT '6E78' FROM DUAL UNION ALL

 11    SELECT '9E-5' FROM DUAL UNION ALL

 12    SELECT '3,E6' FROM DUAL UNION ALL

 13    SELECT '-,45E7' FROM DUAL UNION ALL

 14    SELECT '4,' FROM DUAL

 15  );

 

TO_NUMBER(C)        WIN        MCM    LOOSEXP

------------ ---------- ---------- ----------

           1          1          1          1

           2          1          0          0

         3,4          1          0          0

          -5          1          0          0

  6,0000E+78          1          0          0

      ,00009          1          0          0

     3000000          1          0          0

    -4500000          1          0          0

           4          1          0          0

 

9 rows selected.  

 

Voilà, voilà, vous constaterez qu'il est toujours très facile de faire un article en repompant les solutions des autres... mais bon vu comme j'ai galéré à retrouver la regexp, ça vaut le coup de la rediffuser, hein !

Et puis pour s'y remettre, faut bien commencer quelque part...

 

EDIT : 

 

Prise en compte du NLS_NUMERIC_CHARACTERS, un grand merci à notre guess star Laurent.

Pas prise en compte par contre des nombres de R barre, on va dire que ça en rentre pas dans les données 'business"


SQL>   SELECT c

  2         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i') then to_number(c) end tnb

  3         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i')then 1 else 0 end win

  4         , case when rtrim(c, '1234567890') is null then 1 else 0 end mcm

  5         , case when regexp_like (c, '^[[:digit:]]$') then 1 else 0 end loosexp

  6     FROM (

  7       SELECT '1' c FROM DUAL UNION ALL

  8       SELECT '+2'FROM DUAL UNION ALL

  9       SELECT '3,4' FROM DUAL UNION ALL

 10       SELECT '-5' FROM DUAL UNION ALL

 11       SELECT '6E20' FROM DUAL UNION ALL

 12       SELECT '9E-5' FROM DUAL UNION ALL

 13       SELECT '3,E6' FROM DUAL UNION ALL

 14       SELECT '-,45E7' FROM DUAL UNION ALL

 15       SELECT 'E7' FROM DUAL UNION ALL

 16       SELECT '4,' FROM DUAL UNION ALL

 17       SELECT '4.' FROM DUAL UNION ALL

 18       SELECT '3.4' FROM DUAL UNION ALL

 19       SELECT '-.45E7' FROM DUAL UNION ALL

 20       SELECT '3.E6' FROM DUAL

 21     )

 22     CROSS JOIN (SELECT substr(value, 1, 1) d

 23                 FROM nls_session_parameters

 24                 where parameter='NLS_NUMERIC_CHARACTERS');

 

C             TNB        WIN        MCM    LOOSEXP

------ ---------- ---------- ---------- ----------

1               1          1          1          1

+2              2          1          0          0

3,4           3,4          1          0          0

-5             -5          1          0          0

6E20   6,0000E+20          1          0          0

9E-5       ,00009          1          0          0

3,E6      3000000          1          0          0

-,45E7   -4500000          1          0          0

E7                         0          0          0

4,              4          1          0          0

4.                         0          0          0

3.4                        0          0          0

-.45E7                     0          0          0

3.E6                       0          0          0

 

14 rows selected.

 

Elapsed: 00:00:00.09

SQL> alter session set nls_numeric_characters = '.,'

  2  /

 

Session altered.

 

Elapsed: 00:00:00.01

SQL>   SELECT c

  2         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i') then to_number(c) end tnb

  3         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i')then 1 else 0 end win

  4         , case when rtrim(c, '1234567890') is null then 1 else 0 end mcm

  5         , case when regexp_like (c, '^[[:digit:]]$') then 1 else 0 end loosexp

  6     FROM (

  7       SELECT '1' c FROM DUAL UNION ALL

  8       SELECT '+2'FROM DUAL UNION ALL

  9       SELECT '3,4' FROM DUAL UNION ALL

 10       SELECT '-5' FROM DUAL UNION ALL

 11       SELECT '6E20' FROM DUAL UNION ALL

 12       SELECT '9E-5' FROM DUAL UNION ALL

 13       SELECT '3,E6' FROM DUAL UNION ALL

 14       SELECT '-,45E7' FROM DUAL UNION ALL

 15       SELECT 'E7' FROM DUAL UNION ALL

 16       SELECT '4,' FROM DUAL UNION ALL

 17       SELECT '4.' FROM DUAL UNION ALL

 18       SELECT '3.4' FROM DUAL UNION ALL

 19       SELECT '-.45E7' FROM DUAL UNION ALL

 20       SELECT '3.E6' FROM DUAL

 21     )

 22     CROSS JOIN (SELECT substr(value, 1, 1) d

 23                 FROM nls_session_parameters

 24                 where parameter='NLS_NUMERIC_CHARACTERS');

 

C             TNB        WIN        MCM    LOOSEXP

------ ---------- ---------- ---------- ----------

1               1          1          1          1

+2              2          1          0          0

3,4                        0          0          0

-5             -5          1          0          0

6E20   6.0000E+20          1          0          0

9E-5       .00009          1          0          0

3,E6                       0          0          0

-,45E7                     0          0          0

E7                         0          0          0

4,                         0          0          0

4.              4          1          0          0

3.4           3.4          1          0          0

-.45E7   -4500000          1          0          0

3.E6      3000000          1          0          0

 

14 rows selected.

 

 

Elapsed: 00:00:00.04

 

Partager cet article

Published by Pacman - dans SQL
commenter cet article

commentaires

Laurent Schneider 04/07/2013 07:58

essaye pour rire :

alter session set nls_numeric_characters='w,';

Pacman 04/07/2013 11:51



Oui je sais... j'avais pensé à le délimiteur pour soit n'échapper que ceux qui sont magiques de bases ou ne pas échapper ceux qui le deviennent... mais pfiou, la flemme :)



Laurent Schneider 01/07/2013 13:55

Mouais, à voir...

select to_number(' 00 ') from dual

select 'NaN'/12d from dual

select '+Inf'*1f from dual

ok, c'est un peu tordu comme contre-exemple.

Par contre NLS_NUMERIC_CHARACTERS n'a pas toujours la virgule comme séparateur décimal

Laurent Schneider 01/07/2013 12:39

Et ça devrait faire combien to_number('E3') ?

Pacman 01/07/2013 13:31



Rah, t'es dûr, faut que j'essaie de comprendre la regexp, j'y étais allé à la confiance :)


Bon le \d* ne semble correspondre à aucun cas... ça passe si je le remplace comme ça ?


(c,'^(-|\+)?(\d+|\d+,|,\d+|\d*,\d+)(E(-|\+)?\d+)?$','i')