Discussion :

[Herveg]

Bonjour,
J'ai une table avec plus 60 millions de lignes alphanumériques et numériques (456AA,66545,etc..) je souhaite exclure dans une requete tous ce qui numérique donc
je crée une fonction comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE FUNCTION est_nombre (p_chaine IN VARCHAR2)
   RETURN INT
IS
   v_num NUMBER;
BEGIN
   v_num := TO_NUMBER(p_chaine);
   RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
   RETURN 0;
END est_nombre;

Je lance une requête comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select clef from maTable where est_nombre(clef)=1;

ce qui doit me sortir uniquement que les numériques.

J'ai constaté dans le résultat qu'il ya toujours des clefs alphanumériques avec comme particularité uniquement la lettre "e" exemple(9e981,9e994, etc..).

J'ai effectué des test :

Avec la fonction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select est_nombre('9e981') from dual;

-->il me renvoie 1 ce qui est faux.

Autre part avec ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select to_number('9e981') from dual;

---> oracle lance cette erreur ora:01722 "nombre non valide".

Est ce que y'a possibilité d'optimiser ma fonction.


Je suis sous Oracle 11G

Merci

[al1_24]

J'ai constaté dans le résultat qu'il ya toujours des clefs alphanumériques avec comme particularité uniquement la lettre "e" exemple(9e981,9e994, etc..).

C'est normal, la lettre E est utilisée dans les notations scientifiques des nombres (19e2 est égal 19 * 10² soit 1900)

Pourquoi se fatiguer à écrire une fonction alors que tu peux faire la même chose avec REGEXP_LIKE ?
regexp_like(ma_colonne, '^[0-9]+$') est vrai si le nombre est un entier.

Pour un nombre décimal, c'est à peine plus compliqué : regexp_like(ma_colonne, '^[0-9]+(' || to_char(0, 'fmd') || '[0-9]+)?'$)

[McM]

Est-ce que tu as des nombres à virgule ?

Si non, alors simplement faire

Pour les nombres

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select clef from maTable where LTRIM(clef, '0123456789') IS NULL

Voir si rajouter AND clef is not null

Pour les non-nombre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select clef from maTable where LTRIM(clef, '0123456789') IS NOT NULL

[Herveg]

C'est normal, la lettre E est utilisée dans les notations scientifiques des nombres (19e2 est égal 19 * 10² soit 1900)

Pourquoi se fatiguer à écrire une fonction alors que tu peux faire la même chose avec REGEXP_LIKE ?
regexp_like(ma_colonne, '^[0-9]+$') est vrai si le nombre est un entier.

Pour un nombre décimal, c'est à peine plus compliqué : regexp_like(ma_colonne, '^[0-9]+(' || to_char(0, 'fmd') || '[0-9]+)?'$)

Vous avez raison, j'ai pas pensé avec les expressions régulières.

Merci pour vos contributions

[Herveg]

Est-ce que tu as des nombres à virgule ?

Si non, alors simplement faire

Pour les nombres

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select clef from maTable where LTRIM(clef, '0123456789') IS NULL

Voir si rajouter AND clef is not null

Pour les non-nombre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select clef from maTable where LTRIM(clef, '0123456789') IS NOT NULL

non j'ai pas virgule.

Merci j'opte pour les expressions régulières c'est plus simple

[McM]

regexp est plus lourd (10x plus lent )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DECLARE
d1 number; d2 NUMBER; v NUMBER;
BEGIN
	FOR i IN 1..5
  LOOP
	d1 := DBMS_UTILITY.get_time;
SELECT COUNT(*) INTO v FROM ARTICLE WHERE REGEXP_LIKE(article, '^[0-9]+$');
  d2 := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.put_line('REGEXP '|| v ||' :'|| (d2-d1));
	END LOOP;
	FOR i IN 1..5
  LOOP
	d1 := DBMS_UTILITY.get_time;
SELECT COUNT(*) INTO v FROM ARTICLE WHERE LTRIM(article, '0123456789') IS NULL;
  d2 := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.put_line('LTRIM '|| v ||' :'|| (d2-d1));
	END LOOP;
END;
 
REGEXP 693333 :178
REGEXP 693333 :169
REGEXP 693333 :170
REGEXP 693333 :170
REGEXP 693333 :172
LTRIM 693333 :12
LTRIM 693333 :12
LTRIM 693333 :11
LTRIM 693333 :12
LTRIM 693333 :12