IS_NUMBER : détection des NUMBER déguisés en CHAR

Une fonction bien utile à utiliser dans les programmes pour détecter si une chaîne est en fait un NUMBER déguisé...

Par exemple, on me fournit une table avec une colonne varchar2 qui contient beaucoup de valeurs. Je soupçonne une erreur de conception, ce champ aurait-il pu être défini en NUMBER ?

Je peux rechercher s'il existe des lignes dans la table où la fonction me retourne 0.

CREATE OR REPLACE FUNCTION is_number(string_in VARCHAR2)
RETURN NUMBER
IS
val number;
BEGIN
val := TO_NUMBER (string_in);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN RETURN 0;
END;
/

SQL> CREATE TABLE toto(a VARCHAR2(100));

Table created.

SQL> SELECT value FROM v$nls_parameters WHERE parameter='NLS_NUMERIC_CHARACTERS';

VALUE
----------------------------------------------
,.

SQL> INSERT INTO toto VALUES ('1564');

1 row created.

SQL> INSERT INTO toto VALUES ('11E3');

1 row created.

SQL> INSERT INTO toto VALUES ('0,12');

1 row created.

SQL> SELECT CASE
  2     WHEN (is_number(a) = 0) then 'CHAR'
  3     WHEN (is_number(a) = 1) then 'NUMBER'
  4     END AS WHAT,
  5     COUNT(*) FROM toto GROUP BY is_number(a);

WHAT     COUNT(*)
------ ----------
NUMBER          3
 
SQL> INSERT INTO toto VALUES ('0.12');

1 row created.

SQL> INSERT INTO toto VALUES ('ABCD');

1 row created.

SQL> SELECT CASE
  2     WHEN (is_number(a) = 0) then 'CHAR'
  3     WHEN (is_number(a) = 1) then 'NUMBER'
  4     END AS WHAT,
  5     COUNT(*) FROM toto GROUP BY is_number(a);

WHAT     COUNT(*)
------ ----------
NUMBER          3
CHAR            2