Discussion :

[Yennick]

bonjour,
sur sqlserver 2014 et +, je tente de trouver par requête la liste des éléments qui ont autre chose que des caractères majuscule sans accents ou numérique. j'utilise un select trouvé sur le net mais il manque dans le résultat les éléments qui sont en minuscule et caractères accentué.
pouvez vous me dire ce qui cloche ?

SELECT * FROM @example WHERE code like '%[^A-Z0-9]%'
ou
SELECT * FROM @example WHERE code like '%#[^A-Z0-9]#%' (supposé dire Case sensitive explicite)
ne fonctionnent pas

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DECLARE @example TABLE
(
    CODE VARCHAR(4) PRIMARY KEY
)
INSERT INTO @example  (code)
VALUES (' 1'  ),(' J1' ),('?1'  ),('?2'  ),('?3'  ),('?4'  ),('?5'  ),('?8'  ),('?A1' ),('?C1' ),('?F1' ),
('?J1' ),('àT1' ),('%1' ),('BÉ3' ),('CÉ1' ),('ÀM1' ),('HÉ1' ),('MÉ1' ),('PÉ1' ),('SÉ1' ),('000P'),('000Q'),
('000R'),('000S'),('01a' ),('aa'  ),('AW'  ),('AAAA'),('000Y'),('0010'),('0012'),('0013'),('0015'),('0019'),
('001A'),('001D'),('001F'),('001G'),('001J'),('001K'),('001L'),('001O'),('001P'),('001Q'),('001S')
 
SELECT * FROM @example WHERE code like '%[^A-Z0-9]%'

[aieeeuuuuu]

bonjour,

SELECT * FROM @example WHERE code like '%#[^A-Z0-9]#%' (supposé dire Case sensitive explicite)

Non, pour la sensitivité à la casse et aux accent, il faut spécifier le classement.

essayez par exemple ceci :

Code :

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

1
2
 
SELECT * FROM @example WHERE code like '%[^A-Z0-9]%' COLLATE FRENCH_CS_AS

Si la casse a une importance pour le contenu de votre colonne, il est alors préférable de spécifier le classement directement pour la colonne en question (dans la définition de la table), voire au niveau de la base de données (classement par défaut)

[Yennick]

Bonjour et merci pour votre réponse qui fait avancer vers la solution

Ce sont des données issues de très anciennes bases que l'on souhaite consolider (et recadrer en même temps)

malheureusement cela ne fonctionne pas directement. En effet les caractères accentués semblent inclus dans A-Z ce qui fait que les codes tel que 'BÉ3' sont considérés comme bons.
j'ai du écrire comme ceci.

Code :

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

SELECT * FROM @example WHERE code like '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789]%' COLLATE FRENCH_CS_AS

bien que fonctionnel je ne trouve pas ça très élégant

[aieeeuuuuu]

En effet, dans le classement FRENCH_CS_AS, le 'É' est classé entre le E, et le F, et donc a fortiori entre le A et le Z...
Il en est d’ailleurs de même pour les minuscules, qui sont "mélangées" au majuscules (e est entre D et E).

La solution que vous avez trouvée me semble bien, et a l'avantage d'être explicite.

si vous voulez vraiment garder le principe des plages, alors il faudra un classement plus strict, je pense que ça pourrait fonctionner avec FRENCH_BIN

[LapinClair]

Très bon article sur les collations de SqlPro. (comme d'habitude d'ailleurs !!!)
http://sqlpro.developpez.com/cours/s...er/collations/

[SQLpro]

Mieux vaut utiliser cette formule :

Code :

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

1
2
3
SELECT * 
FROM   @example 
WHERE  code NOT LIKE REPLICATE('[A-Z0-9]', LEN(code)) COLLATE French_CI_AS

A +

[LapinClair]

Bonjour,

SQLpro : pourquoi cette reformulation est-elle meilleure ?

[SQLpro]

Parce que celle que l'on vous a donné sont fausses.

Votre message initial :

... je tente de trouver par requête la liste des éléments qui ont autre chose que des caractères majuscule sans accents ou numérique ...

Donc :
ABCDÉFG --> doit sortir
aBCDEFG --> doit sortir
ABC-123 --> doit sortir
ABCDEFG --> ne doit pas sortir
ABC123 --> ne doit pas sortir

Exemple :

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
WITH T AS 
(
SELECT 'ABCDÉFG' AS DATA_TEST
UNION ALL
SELECT 'aBCDEFG'
UNION ALL
SELECT 'ABC-123'
UNION ALL
SELECT 'ABCDEFG'
UNION ALL
SELECT 'ABC123'
)
SELECT 'TEST SQLpro', *
FROM   T
WHERE  DATA_TEST COLLATE French_BIN NOT LIKE REPLICATE('[A-Z0-9]', LEN(DATA_TEST)) 
UNION ALL
SELECT 'TEST Yennick', *
FROM   T
WHERE  DATA_TEST like '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789]%' COLLATE FRENCH_CS_AS
UNION ALL
SELECT 'TEST aieeeuuuuu', *
FROM   T
WHERE  DATA_TEST like '%[^A-Z0-9]%' COLLATE FRENCH_CS_AS

D'ailleurs la mienne aussi car il faut utiliser la collation French_BIN.... Je n'avais pas eu le temps de tester.

D'autre part faites des tests de performance. Vous m'en direz la différence.

A +

[aieeeuuuuu]

C'est bien entendu plus rapide avec FRENCH_BIN (de l'ordre de trois fois plus rapide, mais comme les résultats ne sont pas les mêmes...)

Pour ce qui est de la comparaison des trois solutions :

jeu d'essai :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE example 
(
    CODE VARCHAR(4) 
)
INSERT INTO example  (code)
VALUES (' 1'  ),(' J1' ),('?1'  ),('?2'  ),('?3'  ),('?4'  ),('?5'  ),('?8'  ),('?A1' ),('?C1' ),('?F1' ),
('?J1' ),('àT1' ),('%1' ),('BÉ3' ),('CÉ1' ),('ÀM1' ),('HÉ1' ),('MÉ1' ),('PÉ1' ),('SÉ1' ),('000P'),('000Q'),
('000R'),('000S'),('01a' ),('aa'  ),('AW'  ),('AAAA'),('000Y'),('0010'),('0012'),('0013'),('0015'),('0019'),
('001A'),('001D'),('001F'),('001G'),('001J'),('001K'),('001L'),('001O'),('001P'),('001Q'),('001S');
 
INSERT INTO example
    SELECT TOP(10000000) CODE
    FROM example AS e, sys.objects AS o, sys.objects AS o2, sys.objects AS o3

Solution SQLPro

Code :

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

1
2
 
SELECT count(*) FROM   example WHERE  code COLLATE French_BIN NOT LIKE REPLICATE('[A-Z0-9]', LEN(code))

Temps UC = 4540*ms, temps écoulé = 4694*ms.

Solution Aieeeuuuuu :

Code :

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

SELECT COUNT(*) FROM example WHERE code LIKE '%[^A-Z0-9]%' COLLATE FRENCH_BIN

Temps UC = 3885*ms, temps écoulé = 4165*ms

solution TEST Yennick :

Code :

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

SELECT COUNT(*) FROM example WHERE code like '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789]%' COLLATE FRENCH_BIN

Temps UC = 9328*ms, temps écoulé = 9657*ms.