Eclatement Champs Adresse Client

**fatim.rtout** · 18/12/2008, 14h43

Bonjour,

Je suis confrontée à un problème, Je dois eclater un champs adresse client d'une table sur deux champs Numero de la rue et Nom de la rue, le point bloquant c'est que ce champs n'est pas structuré.
Exemple Table1 Champs Adresse,
2, rue de la paix
152 rue de la paix
12 avenue de la gloire
5 bis bd de xxxx
6 - 8 boulevard xxxxx

Je dois eclater ces chaines de caractere sur deux champs suivants dans la table2:
Numero de Rue Nom Rue
2 rue de la paix
152 rue de la paix
12 avenue de la gloire
5 bis bd de xxxx
6-8 boulevard xxxxx

Merci de votre aide.

**CinePhil** · 18/12/2008, 15h10

Quand vous dites "je dois" :
- c'est un exercice ?
- vous devez absolument faire des tris selon (rue puis numéro) ?
- un chef fou qui veut vous embêter ?
- vous êtes masochiste ?

Parce que hormis la deuxième proposition, je n'en vois vraiment pas l'intérêt !

**fatim.rtout** · 18/12/2008, 15h27

Dans le cadre d'une refonte d'un SI et la migration de données vers le nouveaux SI que je suis contraite à faire cette fameuse operation !
Il ne s'agit pas d'un tri mais d'un eclatement de ce champs adresse avec tout ce qu'il peut contenir comme informations en deux type d'information, Numero et Nom rue.

Merci de votre aide

**Waldar** · 18/12/2008, 15h49

Il vous faudra regarder du côté des expressions régulières.

**SQLpro** · 18/12/2008, 23h21

Sans parler d'expression régulière qui au niveau des perfs sont lamentables, il suffit de créer une table contenant toutes les voies et de faire une jointure entre cette table et la rue à l'aide d'un like. A partir de là, vous saurez que ce qu'il y a avant est le N° et ce qu'il y a après est le nom de la rue.

Démonstration...

1) votre table, un peu bidon !

Code :

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

1
2
3
4
5
6
7
CREATE TABLE Table1 (Adresse VARCHAR(64))
 
INSERT INTO Table1 VALUES ('2, rue de la paix')
INSERT INTO Table1 VALUES ('152 rue de la paix')
INSERT INTO Table1 VALUES ('12 avenue de la gloire')
INSERT INTO Table1 VALUES ('5 bis bd de xxxx')
INSERT INTO Table1 VALUES ('6 - 8 boulevard xxxxx')

2) la table des nomenclatures de voies :

Code :

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

1
2
3
4
5
6
7
CREATE TABLE T_NOMENCLATURE_VOIE_NCV
(NCV_NOM   VARCHAR(16))
 
INSERT INTO T_NOMENCLATURE_VOIE_NCV VALUES ('rue')
INSERT INTO T_NOMENCLATURE_VOIE_NCV VALUES ('avenue')
INSERT INTO T_NOMENCLATURE_VOIE_NCV VALUES ('bd')
INSERT INTO T_NOMENCLATURE_VOIE_NCV VALUES ('boulevard')

3) la requête magique !

Code :

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

1
2
3
4
5
6
7
8
SELECT SUBSTRING(Adresse, 1, POSITION(NCV_NOM, Adresse) - 1) AS NUM, 
       NCV_NOM AS VOIE, 
       SUBSTRING(Adresse, POSITION(NCV_NOM, Adresse) + CHARACTER_LENGTH(NCV_NOM),
                          CHARACTER_LENGTH(Adresse) - POSITION(NCV_NOM, Adresse)
                          + CHARACTER_LENGTH(NCV_NOM)) AS NOM_VOIE
FROM   Table1 AS T
       LEFT OUTER JOIN T_NOMENCLATURE_VOIE_NCV AS N
            ON T.Adresse LIKE '%' + NCV_NOM + '%'

4) le résultat :

Code :

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

1
2
3
4
5
6
7
NUM                    VOIE             NOM_VOIE            
---------------------- ---------------- --------------------
2,                     rue               de la paix
152                    rue               de la paix
12                     avenue            de la gloire
5 bis                  bd                de xxxx
6 - 8                  boulevard         xxxxx

A +

**Antoun** · 19/12/2008, 00h54

Avec les LIKE, ça va être assez difficile d'éviter les surprises avec des noms de voie du genre Jacques Rueff, Abd-el-Kader, etc. Je conseillerais donc effectivement la solution de SQLpro, mais avec des regex à la place des LIKE. Quant à la faible performance... il s'agit d'une refonte et non pas d'un traitement à effectuer régulièrement, donc la performance n'est pas vraiment l'enjeu.

**CinePhil** · 19/12/2008, 09h20

N'empêche que je ne vois toujours pas l'intérêt d'une telle séparation des éléments de l'adresse !

Empêcher la saisie de futures adresses fausses ?
Ça oblige à maintenir à jour une base des voies existantes, potentiellement pour les 36000 et quelques communes de France, y compris les routes intercommunales qui commencent généralement dans une commune et se terminent dans une autre. Bonjour le boulot ! Il y a fort à parier que ce ne sera jamais à jour et si la saisie est bloquée à cause de ça, vous allez avoir les utilisateurs sur le dos ! Bon courage !

Récupérer d'éventuels doublons ?
Qui vous dit que M. Jean Dupont, habitant 25 Boulevard de la Villette à Paris et enregistré en base de données le 10 juin 2001 soit le même que Jean Dupont, habitant au 25 bd de la Villette à Paris et enregistré le 15/01/2008 ?

Que ferez-vous si des adresses actuellement enregistrées sont fausses ? Êtes-vous sûr de pouvoir les corriger pour qu'elles rentrent dans votre moule ?

On a besoin d'une adresse pour quoi ?
A mon avis, dans plus de 90% des cas, pour envoyer du courrier.
Que l'adresse soit écrite '25 bd' ou '25 boulevard' ou '25 Boulevard' ou '25, bd' ou '25, boulevard' ne changera rien au fait que la lettre arrivera quand même. Et comme j'ai signifié précédemment, l'adresse ne sera qu'un indicateur de détection de doublons potentiel mais ne sera pas le critère suffisant pour juger si c'est un doublon réel.

Bref, cette séparation me semble compliquer les choses pour un intérêt bien mince. Réfléchissez bien à l'utilité avant de figer votre modèle de la sorte et les programmes qui en découleront.

**Antoun** · 19/12/2008, 09h48

Pour rejoindre Phil... une problématique classique est la normalisation d'adresse. Il existe même des outils techniques et des prestataires spécialisés là-dedans, et cela répond à une vraie logique pour des sociétés qui ont une activité centrée sur la distribution ou le marketing direct.

Mais ici, s'il s'agit juste de sortir le n° et de laisser tout le reste en vrac... disons que je suis curieux de savoir à quel besoin ça correspond.

**elbj** · 19/12/2008, 12h01

Et la technique du stagiaire ?

Code :

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

EXEC P_EclaterChampsAdresse WITH Stagiaire

Bon, d'accord, je sors !

Cela dit la technique de SQLPro est un très bon début. Mais elle n'affranchit pas d'un bon pointage ensuite.

+++

**SQLpro** · 19/12/2008, 14h20

Quelques remarques intéressantes

Empêcher la saisie de futures adresses fausses ?
Ça oblige à maintenir à jour une base des voies existantes, potentiellement pour les 36000 et quelques communes de France, y compris les routes intercommunales qui commencent généralement dans une commune et se terminent dans une autre. Bonjour le boulot ! Il y a fort à parier que ce ne sera jamais à jour et si la saisie est bloquée à cause de ça, vous allez avoir les utilisateurs sur le dos ! Bon courage !

Exactement !
Il existe des sociétés qui vendent du recoupement d'adresses par l'intermédiaire de services web en fournissant une nomenclature de toutes les voies et de l'espace de numérotation. J'ai mis en oeuvre de telles techniques pour des sites web marchand de grande ampleur fin 90 début 2000.

Avec les LIKE, ça va être assez difficile d'éviter les surprises avec des noms de voie du genre Jacques Rueff, Abd-el-Kader, etc. Je conseillerais donc effectivement la solution de SQLpro, mais avec des regex à la place des LIKE.

Il y a une amélioration bien simple à faire à la requête que j'ai proposée pour que sans les expressions régulières, on arrive à tout les coups. Il suffit de constater que la voie sera toujours le premier élément joint dans l'odre de lecture.
Ainsi avec :
"33 avenue Jacques Rueff"
On obtiendra deux lignes du fait de la jointure. il ne suffit plus que de prendre la première. C'est facile si l'on dispose des fonctions de fenêtrage. Sinon, il faut utiliser le min de la position.

Vous voulez toujours faire de l'itératif, alors qu'avec un peu de réflexion, et une vision ensembliste ceci sera efficace et concis... pour ne pas dire élégant !

A +

**Antoun** · 19/12/2008, 14h30

Envoyé par SQLpro

...
Vous voulez toujours faire de l'itératif, alors qu'avec un peu de réflexion, et une vision ensembliste ceci sera efficace et concis... pour ne pas dire élégant !

Je suis d'accord avec toi sur l'itératif (

). Mais pourquoi considères-tu que regex = itératif ?

**SQLpro** · 19/12/2008, 14h42

Toute fonction externe (et le REGEX en est une) oblige le moteur SQL à lire la table séquentiellement. Donc de faire du fichier COBOL !
Certaines fonctions purement SQL peuvent être optimisées par le moteur (ce n'est pas toujours le cas et cela dépend fortement de l'intelligence du moteur).

Voici la solution en SQL Server :

Code :

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

1
2
3
-- les nouvelles données censées planter...
INSERT INTO Table1 VALUES ('17 boulevard Jacques rueff')
INSERT INTO Table1 VALUES ('147 bd Abd-el-Kader')

Code :

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

1
2
3
4
5
6
7
8
9
10
11
-- la requête miracle :
SELECT NUM, VOIE, NOM_VOIE
FROM   (SELECT SUBSTRING(Adresse, 1, CHARINDEX(NCV_NOM, Adresse) - 1) AS NUM, 
               ROW_NUMBER() OVER(PARTITION BY Adresse ORDER BY CHARINDEX(NCV_NOM, Adresse)) AS N,
               NCV_NOM AS VOIE, 
               SUBSTRING(Adresse, charindex(NCV_NOM, Adresse) + LEN(NCV_NOM), 
                                  LEN(Adresse) - CHARINDEX(NCV_NOM, Adresse) + LEN(NCV_NOM)) AS NOM_VOIE
        FROM   Table1 AS T
               LEFT OUTER JOIN T_NOMENCLATURE_VOIE_NCV AS N
                    ON T.Adresse LIKE '%' + NCV_NOM + '%') AS T
WHERE   N = 1

Code :

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

1
2
3
4
5
6
7
8
9
10
-- le résultat :
NUM               VOIE             NOM_VOIE
----------------- ---------------- ----------------
12                avenue           de la gloire
147               bd               Abd-el-Kader
152               rue              de la paix
17                boulevard        Jacques rueff
2,                rue              de la paix
5 bis             bd               de xxxx
6 - 8             boulevard        xxxxx

A +

**fatim.rtout** · 19/12/2008, 15h48

Merci pour vos réponses.

Vos solutions concernent-elles Sql Server?
Ma base est sous oracle, la fonction POSITION ne fonctionne pas.

A+

**SQLpro** · 19/12/2008, 22h36

Il existe certainement un équivalent !

A +

**Antoun** · 19/12/2008, 23h37

Envoyé par SQLpro

Il existe certainement un équivalent !

A +

Il s'agit d'INSTR, en inversant les arguments.

**pacmann** · 22/12/2008, 10h06

Envoyé par SQLpro

Toute fonction externe (et le REGEX en est une) oblige le moteur SQL à lire la table séquentiellement. Donc de faire du fichier COBOL !

Intéressant !
Mais SQLPro, Peut on avoir des détails, ou un exemple ?
Comment avec un filtre en %expr% peut-on s'y prendre autrement que séquentiellement ?
Sachant que ce type de recherche empêche l'utilisation d'un index "approprié". Et je doute également que cela permette d'utiliser des algorithmes merge sort ou hash...

Merci !

**SQLpro** · 23/12/2008, 00h47

Vous remarquerez que dans la requête que j'ai donné on ne s'intéresse qu'au cas ou N=1. Il est donc inutile pour l'optimiseur de continuer à explorer la chaîne à l'aide du like si un premier résultat a été trouvé.
Ceci n'est possible que si la sémantique de la fonction est connue du moteur SQL, ce qui est impossible à faire avec des fonctions externes...

A +

**CinePhil** · 23/12/2008, 10h00

Encore quelques petites remarques...

A moins que votre base de données ne contienne que des adresses françaises, n'oubliez pas aussi de tenir compte du fait que les adresses ne sont pas toutes formées de la même manière.

Quelques pièges :
- "Baker Street" ==> le type de voie est après le nom de la voie ;
- "Broadway Avenue" ==> le type de voie est identique au français mais est placé après le nom de la voie ;
- Il me semble que dans d'autres pays, on écrit l'adresse avec le numéro dans la voie après le nom et le type de voie ;
- Il existe des adresses sans numéro, à la campagne notamment.

Encore une fois, vérifiez l'utilité du boulot que représente la manipulation qu'on vous demande de faire !

EDIT : J'ajoute une adresse pour le plaisir :

Envoyé par developpez.com

DEVELOPPEZ LLC
113 Barksdale Professional center
Newark, DE 19711-3258, USA

Il est où le type de voie ?