Recherche varchar identiques à un caractère près

**yarold** · 25/07/2012, 10h32

Bonjour à tous,

Sur une base Oracle 11GR2, je souhaite faire un select sur une colonne en varchar qui me retourne toutes les éléments identiques à un caractère près.
J'essaie d’approfondir la piste en utilisant les regexp mais je n'ai pas abouti

ex de résultats :

SELECT NOM FROM matable ...

'MICKAEL'
'MICHAEL'
'ARRIVAGE'
'ARRIMAGE'

Si quelqu'un a déjà fait cela ?
Merci d'avance

**ojo77** · 25/07/2012, 10h52

Voulez vous ce type de résultat ou un autre ?

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
SQL> with t as ( select 'michael' c1, 'mickael' c2 from dual
  2              union all
  3              select 'michael'   , 'mickaela'   from dual
  4              union all
  5              select 'michael'   , 'mitchell'   from dual
  6              union all
  7              select 'michael'   , 'mickey'     from dual
  8              union all
            select 'arrivage'  , 'arrimage'   from dual )
  9   10  select c1
 11       , c2
 12      , replace(translate(c1,c2,lpad(' ',length(c2)) ),' ') diff
 13      , length(replace(translate(c1,c2,lpad(' ',length(c2)) ),' ')) tdiff
 14  from t
 15  /
 
C1       C2       DIFF                                  TDIFF
-------- -------- -------------------------------- ----------
michael  mickael  h                                         1
michael  mickaela h                                         1
michael  mitchell a                                         1
michael  mickey   hal                                       3
arrivage arrimage v                                         1

**vmolines** · 25/07/2012, 10h53

Bonjour,

La Distance de Levenshtein devrait faire l'affaire.

Implémentez la fonction en PLSQL et utilisez là dans une requête de ce type :

Code :

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

1
2
3
4
5
SELECT T1.NOM 
FROM matable T1
INNER JOIN matable T2 
ON LEVENSHTEIN(T1.NOM, T2.NOM) = 1
AND T1.ID <> T2.ID

**LBO72** · 25/07/2012, 10h54

Bonjour,
A part le faire par du PlSql, je ne vois pas.
Cdlt,
LBO 72.

**Bibeleuh** · 25/07/2012, 11h08

Sinon c'est pas la peine d'implémenter une fonction car le package UTL_MATCH contient déjà de telles fonctions :

Exemple : la fonction EDIT_DISTANCE(VAR1,VAR2) qui te retourne la distance (le nombre de changements à faire pour que les 2 chaines soient égales)

Code :

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

1
2
3
4
 
SELECT UTL_MATCH.EDIT_DISTANCE('MICKAEL', 'MICHAEL')
AS DISTANCE
FROM DUAL;

Code :

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

1
2
DISTANCE
1

**ojo77** · 25/07/2012, 11h19

tout dépend ce que l'on veut faire (je ne connaissait pas utl_match, au passage, merci

)

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
with t as ( select 'michael' c1, 'mickael' c2 from dual
            union all
            select 'michael'   , 'mickaela'   from dual
            union all
            select 'michael'   , 'mitchell'   from dual
            union all
            select 'michael'   , 'mickey'     from dual
            union all
            select 'oracle'    , 'orkale'     from dual
            union all                      
            select 'oracle'    , 'creola'     from dual
            union all                      
            select 'oooooo'    , 'creola'     from dual
            union all                      
            select 'arrivage'  , 'arrimage'   from dual )        
select c1
     , c2
     , replace(translate(c1
                        ,c2
                        ,lpad(' '
                             ,length(c2)) )
              ,' ')||' - '||replace(translate(c2
                                             ,c1
                                             ,lpad(' '
                                                  ,length(c1)) )
                                   ,' ') diff_let
     , abs(length(c1)-length(c2)) diff_len
     , (nvl(length(replace(translate(c1,c2,lpad(' ',length(c2)) ),' ')),0)
       +nvl(length(replace(translate(c2,c1,lpad(' ',length(c1)) ),' ')),0))/2 tdiff
     , nvl((nvl(length(replace(translate(c1,c2,lpad(' ',length(c2)) ),' ')),0)
           +nvl(length(replace(translate(c2,c1,lpad(' ',length(c1)) ),' ')),0))/2
          +abs(length(c1)-length(c2)),0) tdiff_l
     , UTL_MATCH.EDIT_DISTANCE(c1,c2) dist
from t
/

Qui a pour résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
C1       C2       DIFF_LET          DIFF_LEN      TDIFF    TDIFF_L       DIST
-------- -------- --------------- ---------- ---------- ---------- ----------
michael  mickael  h - k                    0          1          1          1
michael  mickaela h - k                    1          1          2          2
michael  mitchell a - t                    1          1          2          3
michael  mickey   hal - ky                 1        2.5        3.5          3
oracle   orkale   c - k                    0          1          1          2
oracle   creola    -                       0          0          0          4
oooooo   creola    - crela                 0        2.5        2.5          5
arrivage arrimage v - m                    0          1          1          1

en clair, veut-on garder les anagrammes (oracle et creola), veut-on garder les mots qui ont les mêmes lettres à une près quelle que soit leur longueurs (michael et mitchell) ...

**LBO72** · 25/07/2012, 11h22

Respects monsieur vmolines :-) je ne connaissais absolument pas ce package.
Merci pour ce super site dans lequel on apprend tous les jours

Merci bibeleuh pour l'info sur le package, par contre je ne comprends pas il me retourne ce résultat pour le test ci-dessous :

Code :

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

1
2
3
4
5
6
SELECT UTL_MATCH.EDIT_DISTANCE('MICKAEL', 'LMICHAE')
AS DISTANCE
DISTANCE
----------
            3
FROM DUAL;

**yarold** · 25/07/2012, 11h39

ce qui m'interesse c'est vraiment le dist = 1, je ne connaissais pas UTL_MATCH.EDIT_DISTANCE et je vais donc m'en servir.

Merci beaucoup à tous et en particulier Bibeleuh.

**Bibeleuh** · 25/07/2012, 11h41

Envoyé par LBO72

Merci bibeleuh pour l'info sur le package, par contre je ne comprends pas il me retourne ce résultat pour le test ci-dessous :

Code :

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

1
2
3
4
5
6
7
SELECT UTL_MATCH.EDIT_DISTANCE('MICKAEL', 'LMICHAE')
AS DISTANCE
DISTANCE
----------
            3
FROM DUAL;

Pour passer de LMICHAE à MICKAEL :

Etape 1 : LMICHAE
Etape 2 : MICHAEL
Etape 3 : MICKAEL

Donc t'as bien une distance de 3 entre les deux, car 3 ~~permutations~~ étapes à faire