Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
27 mai 2010 4 27 /05 /mai /2010 12:12

Aujourd'hui, un thème assez banal : tester si une chaîne de caractères est un nombre !
C'est à priori pas super passionnant, mais l'objectif ici est surtout de détailler l'une des méthodes qui est souvent baclée.


On va donc explorer rapidement trois possibilités :
1) Essayer... et rattraper l'exception
2) Tester avec une expression régulière
3) La méthode translate

 

 

1) La grande classique

 

La méthode que l'on retrouve le plus souvent consiste à créer une fonction qui tente simplement de caster la chaîne en nombre.
Si l'opération échoue, on rattrappe l'exception.

 

La fonction :

  

CREATE OR REPLACE FUNCTION IS_NUMBER ( p_str IN VARCHAR2 ) RETURN NUMBER

IS

v_dummy NUMBER;

 

BEGIN

  

   v_dummy := TO_NUMBER(p_str);

 

  

Le test :

  

SQL> SELECT is_number('robert') as notnum, is_number('123,43') as num

  2  FROM DUAL

  3  /

 

    NOTNUM        NUM

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

        -1          0

 

 

 

Cependant, on entend souvent rétorquer à cette méthode "Mais euh, je n'ai pas le droit de créer de fonction sur ma base !"
D'où la suite...

 

2) Les expression régulières

 

Avec les expression régulières, on peut tester la "tronche" de la chaîne : plus que les caractères qu'elle contient, l'expression régulière teste également "l'agencement"...
A partir d'Oracle 10g, on a droit à REGEXP_LIKE !

 

 

 

Note importante : REGEXP_LIKE renvoie un booléen, qui n'est pas un type qui peut être renvoyé par l'instruction SELECT d'Oracle.
C'est pour cela qu'il faut la traiter avec un CASE WHEN...


3) Translate !

 

La méthode TRANSLATE, c'est simple :
En gros, on prend la chaîne, on remplace tous les chiffres par des blancs, et s'il ne reste que des blancs à la fin, c'est un nombre.

 
   

SELECT CASE WHEN trim(translate('1234,5', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as num

, CASE WHEN trim(translate('robert', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as notnum

FROM DUAL

/

 

       NUM     NOTNUM

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

         1          0

 

Seulement voilà, le résultat est assez inexact, car on teste les caractères contenus, mais pas le séquencement !


Et c'est en fait la seule raison plus ou moins valable de s'étendre sur ce sujet maintes fois traité... faisons donc l'effort de détailler cela en détail.

 

a. Les espaces


Dans le premier exemple de translate, on ne teste pas les espaces.
Pourtant, les formats disponibles pour to_number ne permettent pas d'intercaler des espaces entre les chiffres...

 

Par exemple, le translate donne :

 

SQL> ed

Úcrit fichier afiedt.buf

 

  1  SELECT CASE WHEN trim(translate('12 345', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res

  2* FROM DUAL

SQL> /

 

       RES

----------

         1

 

Alors que le to_number vautre :

  

 

SQL> SELECT to_number('12 345') FROM dual;

SELECT to_number('12 345') FROM dual

                 *

ERREUR Ó la ligne 1 :

ORA-01722: invalid number

 

En fait, les espaces "trailing" et "leading" sont les seuls acceptés.
On peut donc simplement comparer la taille de la chaîne "trimée" à la taille de la chaine dépouillée de tous les espaces.

 

 

b. Nombre d'occurences des caractères spéciaux

 

La version basique de la méthode translate se contente d'accepter une liste de caractères.
Cependant, certains caractères tels que le séparateur de décimales ou le signe doivent apparaître au maximum une fois.

 

SQL> ed

Úcrit fichier afiedt.buf

 

  1  SELECT CASE WHEN trim(translate(' 12 345 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE coalesce(length(trim(' 12 345 ')), 0) - coalesce(length(replace(' 12 345 ', ' ', '')), 0)

  3  WHEN 0 THEN 1 ELSE 0 END as res2

  4* FROM DUAL

SQL> /

 

       RES       RES2

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

         1          0

   

Pour tester le nombre d'occurences d'un caractère, il suffit de comparer la longueur de la chaîne de départ à la longueur de la chaîne épurée des dits caractères :

 

  

 

SQL> SELECT CASE WHEN trim(translate('12,34,5 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE WHEN coalesce(length('12,34,5'), 0) - coalesce(length(replace('12,34,5', ',', '')), 0) > 1 THEN 0 ELSE 1 END as res3

  3  FROM DUAL

  4  /

 

       RES       RES3

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

         1          0

  

c. Restrictions sur la position d'un caractère spécifique

 

Les restrictions suivantes doivent être appliquées :
- le caractère "e" de l'écriture scientifique ne doit pas être en dernière position
- les caractères + et - doivent se trouver en première position

  

 

SQL> SELECT CASE WHEN trim(translate('12,34,5 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE coalesce(length('12345e'), 0) - coalesce(instr('12345e', 'e'), 0)  WHEN 0 THEN 0 ELSE 1 END as res4,

  3  CASE WHEN coalesce(instr('123+45', '+'), 0)  > 1 THEN 0 ELSE 1 END as res5

  4  FROM DUAL

  5  /

 

       RES       RES4       RES5

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

         1          0          0

 

d. La position relative de certains caractères

 

Le séparateur de décimales doit apparaître avant le caractère "e" :

 

 

 

SQL> SELECT CASE WHEN trim(translate('12,34,5 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE WHEN coalesce(instr('12e3,45', ','), 0) <= coalesce(instr('12e3,45', 'e'), 0)  THEN 1 ELSE 0 END as res4

  3  FROM DUAL;

 

       RES       RES4

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

         1          0

 
Allez, si on combine toutes ces conditions, on devrait y arriver :

 

SQL> WITH testeuh AS
  2  (SELECT '123' AS c FROM DUAL UNION ALL
  3  SELECT '12,3' AS c FROM DUAL UNION ALL
  4  SELECT '+12,3' AS c FROM DUAL UNION ALL
  5  SELECT '+123,1e5' AS c FROM DUAL UNION ALL
  6  SELECT '123+5' AS c FROM DUAL UNION ALL
  7  SELECT '125,5,' AS c FROM DUAL UNION ALL
  8  SELECT '125e,5' AS c FROM DUAL UNION ALL
  9  SELECT '1 125,5' AS c FROM DUAL
 10  )
 11  SELECT c,
 12  CASE WHEN trim(translate(c, '1234567890,+-e', ' ')) IS NULL THEN 1 ELSE 0 END
 13  * CASE coalesce(length(trim(c)), 0) - coalesce(length(replace(c, ' ', '')), 0) WHEN 0 THEN 1 ELSE 0 END
 14  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, ',', '')), 0) > 1 THEN 0 ELSE 1 END
 15  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, '+', '')), 0) > 1 THEN 0 ELSE 1 END
 16  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, '-', '')), 0) > 1 THEN 0 ELSE 1 END
 17  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, 'e', '')), 0) > 1 THEN 0 ELSE 1 END
 18  * CASE coalesce(length(c), 0) - coalesce(instr(c, 'e'), 0)  WHEN 0 THEN 0 ELSE 1 END
 19  * CASE WHEN coalesce(instr(c, '+'), 0)  > 1 THEN 0 ELSE 1 END
 20  * CASE WHEN coalesce(instr(c, '-'), 0)  > 1 THEN 0 ELSE 1 END
 21  * CASE WHEN coalesce(instr(c, ','), 0) <= coalesce(instr(c, 'e'), 0) OR coalesce(instr(c, 'e'), 0) = 0 THEN 1 ELSE 0 END AS isnumeric
 22  FROM testeuh
 23  / 

  

C         ISNUMERIC
-------- ----------
123               1
12,3              1
+12,3             1
+123,1e5          1
123+5             0
125,5,            0
125e,5            0
1 125,5           0 

 

Une grande dédicace à Mr X pour sa contribution.
(Je n'ai pas le droit de le citer...)

 

 

 

 

 

Partager cet article

Published by Pacman - dans SQL
commenter cet article

commentaires

Tony 17/09/2013 23:52

Article très intéressant. D'ordinaire, ce sont plutôt avec des langages de programmation que l'ont peut vérifier si une données est numérique. C'est intéressant de regarder comment traiter le
problème en SQL. Merci pour l'article.

Pacman 18/09/2013 09:57



Merci Tony pour ton commentaire. Idéalement, ce genre de test ne devrait même pas être nécessaire côté base de donnée, puisque les données numériques devraient être stockée en numérique et non en
string (et les contrôles de format fait en entrée sur les interfaces)... mais voilà, la réalité n'est pas toujours idéale :)


J'ai écrit un petit complément récemment sur la version regexp : 


http://pacmann.over-blog.com/article-a-little-more-is_numeric-118822914.html


Bon courage, 


Pacman