Discussion :

[Evenmor]

Bonjour,
Voila que je travail avec oracle et je rencontre un souci.
Je possède une table avec un champ Reference.
Ce champs peut comporter tout type d'information, il s'agit d'un varchar.
Seulement voila, je souhaite récupérer uniquement les champs dont les 2 premiers caracteres sont compris entre 1 et 13.
Existe-t-il une syntaxe pour faire ca?
Merci pour votre aide.

[skuatamad]

Salut,

Tu peux essayer avec :

Code :

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

to_number(substr(tonchamp,1,2)) between 1 and 13

[Invité]

Salut,

Tu peux essayer avec :

Code :

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

to_number(substr(tonchamp,1,2)) between 1 and 13

Oui mais il faut gérer le cas où les deux premiers caractères ne sont pas des chiffres.

[Invité]

une méthode pas forcément la plus habile mais qui marche est d'utiliser cette fonction :

Code :

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

1
2
3
4
5
6
7
8
9
create or replace FUNCTION ISNUMERIC ( X IN VARCHAR2) RETURN NUMBER IS
nb number;
begin
 nb := to_number(substr(tonchamp,1,2));
 return nb;
exception
 when others then
 return 0;
end;

et de lancer cette requête :

Code :

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

1
2
3
select *
from ma_table 
where ISNUMERIC(tonchamp) BETWEEN 1 AND 13 ;

[skuatamad]

Merci Jerome pour la rectification, j'avais pas testé, et effectivement ça plante.
Donc j'ai fait quelques tests et en fait il faudrait être encore moins subtil pour couvrir tous les cas :

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
 
CREATE OR REPLACE FUNCTION ISNUMERIC2 ( X IN VARCHAR2) RETURN NUMBER IS
nb number;
begin
 nb := to_number(substr(X,1,2));
 RETURN nb;
exception
 when others then
	begin
		nb := to_number(substr(X,1,1));
		return nb;
		exception
 			when others then	
 				RETURN 0;
	end;
end;

et les tests :

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
30
31
32
33
34
35
36
37
38
 
SQL> select * from test_o;
 
C
----------
abcdefgh
5erftu
05erftu
10jfjfhrt
 
 
SQL> select * from test_o where to_number(substr(c,1,2)) between 1 and 13;
select * from test_o where to_number(substr(c,1,2)) between 1 and 13
                                     *
ERREUR � la ligne 1 :
ORA-01722: Nombre non valide
 
 
SQL> select * from test_o where substr(c,1,2) between '1' and '13';
 
C
----------
10jfjfhrt
 
SQL> select * from test_o where ISNUMERIC(c) BETWEEN 1 AND 13 ;
 
C
----------
05erftu
10jfjfhrt
 
SQL> select * from test_o where ISNUMERIC2(c) BETWEEN 1 AND 13 ;
 
C
----------
5erftu
05erftu
10jfjfhrt

Evidemment toute proposition subtile est la bien venue

[Evenmor]

Merci pour vos réponse.
N'existe-t-il pas un élément comme dans SQL serveur "Like '[1-13]' " sous oracle?

[mnitu]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SQL> r
  1  select *
  2    from test_o
  3*  where regexp_like(a,'(^[1-9][^0-9]|^1[0-3]|^0[1-9])')
 
A
--------------------
5erftu
05erftu
10jfjfhrt

[Evenmor]

Super, Merci.
On pourrait m'expliquer la syntaxe svp?
J'ai peur de ne pas bien tout comprendre ^^".
Merci en tout cas

[skuatamad]

merci mnitu, la classe comme d'hab

[McM]

Sans les regexp

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
with t as (select '10jfjfhrt' a from dual
union all select '5adsae' from dual
union all select '14asa' from dual
union all select '12222' from dual
union all select '' from dual
union all select 'a' from dual)
select a, to_number(substr(a, 1, LEAST(length(a) - NVL(length(ltrim(a, '0123456789')),0),2))) as nb
 from t
where to_number(substr(a, 1, LEAST(length(a) - NVL(length(ltrim(a, '0123456789')),0),2))) between 1 and 13
 
A	NB
10jfjfhrt	10
5adsae	5
12222	12

[skuatamad]

Bon c'est sûr vous êtes nombreux à avoir la classe sur ce forum
Bien que d'apparence plus lourde, même si plus facile à appréhender, la solution de MCM présente l'avantage d'être "multi version compatible"
cf cette discussion
merci MCM

[mnitu]

Super, Merci.
On pourrait m'expliquer la syntaxe svp?
J'ai peur de ne pas bien tout comprendre ^^".
Merci en tout cas

Il y a un excellent tutorial sur les expressions régulières ici (je suis plutôt novice dans le domaine).

Code :

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

1
2
3
4
5
 
^[1-9][^0-9] – commence (^) par 1, 2, … 9 et non pas suivi (^) par 0, … 9
| - ou
^1[0-3] – commence (^) par 1 suivi de 0, …, 3
|^0[1-9] – ou (|) commence par (^) 0 suivi de 1, … 9

[mnitu]

Tom dit

yes, I know about regexp - that function would take more cpu than plsql does in general... tried it

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
 
SQL> r
  1  WITH t AS (SELECT '10jfjfhrt' a FROM dual
  2  union ALL SELECT '5adsae' FROM dual
  3  union ALL SELECT '14asa' FROM dual
  4  union ALL SELECT '12222' FROM dual
  5  union ALL SELECT '' FROM dual
  6  union ALL SELECT 'a' FROM dual)
  7  Select a from (
  8    Select
  9      case when nvl(replace(translate(substr(a,1,2),'0123456789','000000000'),'0',''),'.') = '.'
 10           then to_number(substr(a,1,2))
 11           when nvl(replace(translate(substr(a,1,1),'0123456789','000000000'),'0',''),'.') = '.'
 12           then to_number(substr(a,1,1))
 13      end nr,
 14      a
 15    from t)
 16  where nr between 1 and 13
 17*
 
A
---------
10jfjfhrt
5adsae
12222

[McM]

Rooohh... un NVL(, '.') = '.' !!
du coup avec select '.' from dual => Invalid number

[mnitu]

Rooohh... un NVL(, '.') = '.' !!
du coup avec select '.' from dual => Invalid number

C'est vrai

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
 
SQL> r
  1  WITH t AS (SELECT '10jfjfhrt' a FROM dual
  2  union ALL SELECT '5adsae' FROM dual
  3  union ALL SELECT '14asa' FROM dual
  4  union ALL SELECT '12222' FROM dual
  5  union ALL SELECT '' FROM dual
  6  union ALL SELECT '.' FROM dual
  7  union ALL SELECT 'a' FROM dual)
  8  Select a from (
  9    Select
 10      case when instr(substr(a,1,2),'.') = 0 and nvl(replace(translate(substr(a,1,2),'0123456789','000000000'),'0',''),'.') = '.'
 11           then to_number(substr(a,1,2))
 12           when SubStr(a,1,1) <> '.' and nvl(replace(translate(substr(a,1,1),'0123456789','000000000'),'0',''),'.') = '.'
 13           then to_number(substr(a,1,1))
 14      end nr,
 15      a
 16    from t)
 17  where nr between 1 and 13
 18*
 
A
---------
10jfjfhrt
5adsae
12222

[Evenmor]

Cool,
Merci pour toutes vos explications et vos aides.
Ca m'a bien aidé à avancer.