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...)
