Discussion :

[sevyc64]

Salut à tous, j'ai un petit soucis avec un filtre where.

Le contexte :
Je fais une reprise de données sur une vieille appli dont les bases sont sous SQLServer.
Dans la nouvelle structure, j'ai un champ d'une table qui ne peut contenir que des caractères autorisés à savoir Alphanumérique sans accent, les /-%+$. et l'espace.
Certaines données actuelles contiennent des caractères donc interdits.

Je veux faire une requête pour sortir les enregistrements dont le champs contient ces caractères interdits, pour pouvoir mettre quelqu'un dessus ensuite pour corriger ces données afin de ne pas bloquer la récupération (il est probable qu'il ne soit pas possible de faire une correction aveugle par une fonction Replace).

N'ayant pas envie de me taper une série de Like dans le Where, je me dis qu'une RegEx doit être capable de faire ça.
Donc récupérer, les enregistrements dont le champs contient un ou plusieurs caractères autres que ceux autorisés.

Il faudrait apparemment que j'utilise une fonction xp_regex_format, c'est bien ça ?

Par contre, je suis bien incapable d'écrire la RegEx elle-même. Un peu d'aide ?

PS : base de prod sous SQLServer2000
Possibilité de travailler sur une copie de test sous SQLServer 208 Express, si besoin.

[aieeeuuuuu]

Bonjour


Vous devriez pouvoir vous en sortir avec un simple LIKE, en utilisant la bonne collation.

Pouvez vous nous fournir un jeu d'essai, avec 5 ou 6 valeurs valides, et 5 ou 6 valeurs invalides (+ raison de l'invalidité)

[aieeeuuuuu]

Dans la nouvelle structure, j'ai un champ d'une table qui ne peut contenir que des caractères autorisés à savoir Alphanumérique sans accent, les /-%+$. et l'espace.

Y a-t-il une contrainte sur cette colonne ?


que donne cette requête (elle devrait vous sortir les valeurs invalides) :

Code SQL :

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

1
2
3
4
 
SELECT LaColonne
FROM LaTableSource
WHERE t COLLATE Latin1_General_BIN LIKE  '%[^0-9A-Za-z/-%+$. ]%'

[sevyc64]

Ok, apparemment on a pas besoin de fonctions particulières pour utiliser une RegEx dans avec LIKE. Déjà, j'ai appris ça.

Pour la contrainte sur la base, sur la base source, il n'y en a pas, sinon ça n'aurait pas pu être saisi. Pour la base de destination, je ne sais pas, je n'y ai pas accès. C'est une base propriétaire, l'import se fait par l'application propriétaire (SAGE L100) via fichiers textes (que je dois générer moi) et c'est l'application qui indique elle-même les erreurs.

Ensuite ta regex n'est pas bonne. Elle me renvoie des champs qui sont corrects. par exemple, elle me renvoie ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
171-5231              bon
AC-162BYILY70H-AR     bon
153-1116-ND           bon
BE113-E Prestation    bon
WG160128BTFHTZ/#      pas bon, #
modèle A              pas bon, accent
modèle C              pas bon, accent
à compléter           pas bon, 2 accents
100815,0006           pas bon, virgule
PD10/8211-0,5         pas bon, virgule
N°1092C               pas bon, °
08 04 374 (41-50)     pas bon, parenthèses

J'ai extrait ces valeurs à la main, ce n'est qu'un échantillon de ce que je peux avoir, la liste n'est pas exhaustive.

[sevyc64]

Pour un premier passage de fichier, j'ai repéré les caractères suivants qui ne seraient pas bons :

# è é à , ( ) = * " °

Pour l'instant, pour mes essais, je fais des remplacements à l'arrache mais au final il faudra analyser chaque cas pour savoir quoi remplacer ou comment codifier autrement.

[SQLpro]

REGEX est non seulement contre performant, mais sujet à des attaques de DOS.

Voici une solution avec like :

Code :

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

CREATE TABLE T (C VARCHAR(256))

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO T VALUES 
('171-5231'), 
('AC-162BYILY70H-AR'),
('153-1116-ND'),
('BE113-E Prestation'), 
('WG160128BTFHTZ/#'), 
('modèle A'), 
('modèle C'), 
('à compléter'),
('100815,0006'),
('PD10/8211-0,5'),
('N°1092C'),
('08 04 374 (41-50)');

Code :

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

1
2
3
SELECT *
FROM T 
WHERE C COLLATE French_CI_AS LIKE REPLICATE('[ abcdefghijklmnopqrstuvwxyz0123456789/~-%+$.]', LEN(C)) ESCAPE '~'

A +

[aieeeuuuuu]

oui, en effet, je n'ai pas fait attention au "-" dans la liste des caractères spéciaux acceptés. le tiret sert a définir des plages, il faut donc le déspécialiser dans la requête que j'ai donnée :

Code SQL :

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

1
2
3
4
 
SELECT LaColonne
FROM LaTableSource
WHERE t COLLATE Latin1_General_BIN LIKE  '%[^0-9A-Za-z/|-%+$. ]%'  ESCAPE '|'

[sevyc64]

aieeeuuuuu >
Ta requête corrigée semble me renvoyer effectivement que des enregistrements qui ont un caractère pas bon. (j'ai pas tout analyser dans le détail)

SQLpro >
Ta requête semble faire l'inverse (des résultats que j'ai, et de ce que j'en comprend), elle ne renvoie que les enregistrements qui sont bons. Hors moi, c'est ceux qui sont mauvais qu'il me faut.

Problème :
J'ai un total de 8067 lignes dans la table
la requête de aieeeuuuuu me renvoie 61 lignes
la requête de SQLpro me renvoie 7172 lignes, laissant supposer 895 lignes incorrectes.

Il faut donc que je recherche ce qui fait cet écart entre 61 et 895 !

[SQLpro]

NOT LIKE !

Faudrait quand même investir un peu de jus de cerveau pour apprendre le minimum en matière de SQL !!!!!

A +

[sevyc64]

Bien sur que j'avais tenté NOT LIKE, sauf que ça me renvoie 157 lignes, d’où investigations.

Pour les lignes manquantes, c'est à dire 738, c'est tout simplement que le champs en question est à Null, ce qui est pas mal, pour une référence censée être obligatoire.

De plus ça me permet de m’apercevoir, que ce champs a été renseigné n'importe comment. Ce qui est censé contenir une référence, contient parfois un commentaire, une adresse mail, ou autres bizarreries.

[aieeeuuuuu]

la requête de SQLpro me renvoie 7172 lignes, laissant supposer 895 lignes incorrectes.

Non, les valeur qui ne sont pas incorrectes, ne sont pas forcément correctes !
Vous devez avoir des NULL

Il faut donc que je recherche ce qui fait cet écart entre 61 et 895 !

[aieeeuuuuu]

notez également la légère différence entre le Pattern de SQLPro et le mien :
Mon pattern considérera 3 caractères invalides que le pattern de SQLPro considérera comme valides :
¹
²
³

(exposant 1, 2 et 3)

C'est très mineur, et à part cela, je ne vois pas de différence sémantique.

[sevyc64]

Oui aieeeuuuuu, ton pattern considère les accents comme valide, mais ils sont invalides, d’où la différence entre 61 lignes invalide avec ton pattern et 157 avec celui de SQLPro

[aieeeuuuuu]

Oui aieeeuuuuu, ton pattern considère les accents comme valide

Non ! vous avez du oublier de préciser la collation : COLLATE Latin1_General_BINCombien avez vous de lignes à NULL pour cette colonne ?

[SQLpro]

Ma requête est d'ailleurs erronée. Voici une correction :

Code :

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

1
2
3
SELECT *
FROM T 
WHERE C COLLATE French_CI_AS LIKE REPLICATE('[ abcdefghijklmnopqrstuvwxyz0123456789/~-~%+$.]', LEN(C)) ESCAPE '~'

A +

[sevyc64]

Non ! vous avez du oublier de préciser la collation : COLLATE Latin1_General_BINCombien avez vous de lignes à NULL pour cette colonne ?

Tu as raison !

mauvais copier/coller (comme toujours) le COLLATE avait sauté

Les 2 requêtes renvoient bien le même résultat, 157 lignes à reprendre.
(en réalité c'est presque toute la base qu'il faudrait reprendre, mais ça c'est pas mon boulot)

SQLpro> ta correction ne change rien dans ce cas particulier mais aurait pu effectivement.

Merci, à vous 2

[aieeeuuuuu]

Ma requête est d'ailleurs erronée. Voici une correction :

Non, je pense qu'elle était bonne, le "%" étant entre crochets [] et donc déjà déspécialisé...