Discussion :

[frantzgac]

Bonjour

Je cherche à grouper les différents opérateurs de messagerie dans une liste d'adresses mails.

Le champ concerné est BAS_ADR

La requête

Code :

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

1
2
select distinct right(BAS_ADR,char_length(BAS_ADR)-(position('@'in BAS_ADR)))   
from MAIL_BASE

donne satisfaction mais distingue les opérateurs identiques à la casse près.

Aussi je tente d'utiliser la clause COLLATE FR_FR_CI_AI pour les regrouper.

En ajoutant la clause après BAS_ADR

Code :

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

1
2
3
 
select distinct right(BAS_ADR collate FR_FR_CI_AI,char_length(BAS_ADR)-(position('@'in BAS_ADR))) 
from MAIL_BASE

j'obtiens ce message token unknown -line 1,column 31


Problème de syntaxe ?

[Michel.Priori]

Bonjour,

Je ne suis pas un spécialiste de Firebird.
Une petite recherche internet m'a convaincu que le mot clé COLLATE ne fait pas partie du produit (il existe bien sous SQL server)

Une piste : https://www.firebirdsql.org/file/doc...collation.html

[SergioMaster]

Je ne suis pas d'accord, COLLATE existe, mais on ne l'utilise pas comme ça

Par exemple

Code :

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

1
2
CREATE TABLE users (
  NAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE FR_FR_CI_AI   ---

Code :

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

ORDER BY LASTNAME COLLATE FR_CA, FIRSTNAME COLLATE FR_CA

Code :

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

1
2
3
WHERE LASTNAME COLLATE FR_CA = :lastnametosearch
--
WHERE UPPER (LAST_NAME COLLATE SV_SV) = 'PAULSEN';

Et encore, cela va dépendre de l'encodage de la base de données qui devra être ISO8859_1 ou à tout le moins la colonne (N.B. Je n'ai jamais essayé un COLLATE dans ce cas)
de plus une adresse mail, à ma connaissance, n'a pas de caractère accentué

Pour ce qui est du SQL

Code :

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

1
2
select distinct right(lower(BAS_ADR),char_length(BAS_ADR)-(position('@'in BAS_ADR))))   
from MAIL_BASE

devrait faire l'affaire
mais je préfère

Code :

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

1
2
select DISTINCT substring(lower(email) FROM position('@' IN email)+1) from clients 
where position('@' IN email)>0 --permettant (en partie) de sauter les erreurs d'adresses

ou

Code :

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

1
2
select DISTINCT substring(lower(email) FROM position('@' IN email)+1) from clients 
where  where Email containing '@' --permettant (en partie) de sauter les erreurs d'adresses

pour vérifier complètement une adresse on utilisera SIMILAR TO ou LIKE

Code :

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

1
2
select DISTINCT substring(lower(email) FROM position('@' IN email)+1) from clients 
where Email SIMILAR TO  '_%@_%._%'  -- à améliorer

[SQLpro]

Selon la norme SQL, l'opérateur COLLATE devrait pouvoir être positionné derrière (donc à droite) chaque expression littérale, qu'elle qu'en soit la forme... Et cet opérateur est indépendant du jeu de caractère....

Mais certains SGBDR "libres" ont du mal à respecter cela... Par exemple PostGreSQL part en erreur avec le LIKE si COLLATE est précisé.
Visiblement FireBird est aussi limité...

A +

[SergioMaster]

Je n'avais pas fini mes essais, par manque d'un fichier test, de plus il manque des informations importantes à savoir :

1. Quelle version de Firebird.
2. Quelle encodage de la base de données.

Firebird 3, Base de Données en UTF8

Code :

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

1
2
select DISTINCT substring(mail COLLATE UNICODE_CI_AI FROM position('@' IN mail)+1) from TEST_MAIL
where mail SIMILAR TO  '_%@_%._%'  -- à améliorer

Selon les tableaux que j'ai pu consulter pour utiliser FR_FR_CI_AI il faut que la base soit encodée ISO8859_1 et il faut une version 2.1 mini.

J'ai profité de ce test pour confirmer ou infirmer qu'une colonne déclarée CHARACTER SET ISO8859_1 dans une base de données UTF8 pouvait utiliser FR_FR_CI_AI, c'est bien le cas
exemple
la colonne freMail est en ISO8859_1

Code :

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

1
2
3
select mail from TEST_MAIL
where mail SIMILAR TO  '_%@_%._%'  -- à améliorer
ORDER BY substring(fremail COLLATE FR_FR_CI_AI FROM position('@' IN fremail)+1)

[frantzgac]

Excellent.

Je complète les informations. Il s'agit de FB 2.5.9 et bien que la base possède le CHARSET NONE elle est ouverte en ISO8859_1

Ces trois requêtes fonctionnent :

Code :

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

1
2
3
select distinct right(lower(BAS_ADR),char_length(BAS_ADR)-(position('@'in BAS_ADR)))  
from MAIL_BASE
order by right(lower(BAS_ADR),char_length(BAS_ADR)-(position('@'in BAS_ADR)))

Code :

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

1
2
3
select DISTINCT substring(lower(bas_adr) FROM position('@' IN bas_adr)+1) 
from MAIL_BASE
where BAS_ADR containing '@'

et celle-ci retourne une ligne de moins qui effectivement est une adresse sans extension.

Code :

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

1
2
select DISTINCT substring(lower(bas_adr) FROM position('@' IN bas_adr)+1) from MAIL_BASE 
where bas_adr SIMILAR TO  '_%@_%._%'

J'ignorais l'existence de CONTAINING, SIMILAR TO qui m'intéresse beaucoup !

Merci