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

[Michel.Priori]

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

re,
n'ayant pas de firebird sous la main je ne peux pas tester

Il me semble que les 2 opérandes sont des variations de LIKE.
Quelles sont les différences (autres que purement syntaxique) ?

[SQLpro]

En SQL CONTAINS (et non pas CONTAINING) est l'opérateur normalisé pour l'indexation textuelle. À me lire : https://blog.developpez.com/sqlpro/p...text_search_no
SIMILAR fait aussi partie de la norme SQL. C'est une option avancée du LIKE qui utilise les principes similaires aux expressions régulières, mais adapté aux jeude données que sont les tables et vue.

Voici ce que dira mon prochain chapitre sur le langage SQL au sujet de SIMILAR :



A +

[SergioMaster]

Il s'agit de FB 2.5.9 et bien que la base possède le CHARSET NONE elle est ouverte en ISO8859_1

Hélas CHARSET NONE est une hérésie historique qu'il est fortement recommandé de ne pas utiliser (c'est indiqué depuis le début de Firebird dans la documentation, tout comme le fait que le propriétaire de la BDD ne devrait pas être SYSDBA.
Ouvrir en ISO8859_1 ne change rien à l'affaire puisqu'il s'agit, dans ce cas de l'achange avec l'application tierce , c'est plus un problème de table de caractère utilisé qu'autre chose. Le problème auarait pu être "contourné" en définissant la colonne avec le CHARSET voulu.
Maintenant, je présume que la question suivante serait : Comment changer le CHARSET d'une base de données et là, cela devient compliqué.

Je n'ai pas assez suivi les évolutions Firebird 3,4,5 pour affirmer que désormais il est facile de changer le CHARSET de la BDD (ou d'ailleurs le SQL Dialect)
Il existe la commande (à vérifier je crois que c'est à partir de FB3)

Code :

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

ALTER DATABASE SET DEFAULT CHARACTER SET  default character set

et celle-ci (FB 2.5 inclus)

Code :

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

1
2
ALTER CHARACTER SET <charset_name>
            SET DEFAULT COLLATION <collation_name>

toutefois via un backup/restore classique, d'expérience, ce n'est pas gardé. N.B. via mes lectures du jour, j'avais peut-être oublié un DATABASE COMMIT; et j'aurai du utilisé nBackup plutôt que le GBAK classique

La modification du jeu de caractères ne fonctionne correctement que si la colonne possède actuellement un jeu de caractères réel (c'est-à-dire autre que NONE ou OCTETS). La conversion de NONE ou OCTETS vers un autre jeu de caractères peut entraîner des erreurs de conversion de chaîne ou des erreurs car le contenu peut ne pas correspondre à vos attentes ou peut contenir des octets non valides dans le jeu de caractères cible

Ante FB.3, la solution préconisée était de créer une nouvelle base et de copier les données. Il existe des utilitaires comme fbcopy ou fbClone (que je n'ai jamais maitrisés et donc aimé), et certainement d'autres, fut un temps j'avais même projeté d'en écrire un en Delphi et grâce aux nouveautés de Firedac. Comme il est possible de se connecter a une base externe, j'ai aussi fait le test en créeant des procédures de copie table par table (plus long quand on a une base de données "conséquente" en nombre de tables.
Passer par exemple de NONE à UTF8 cela ne se fera pas sans mal (sauf à caster les colonnes en passant par WIN1252 pour les colonnes, N.B. je n'ai pas essayé le cast ISO8859_1)