Discussion :

[mecmec]

Salut

Je montre ma commande SQL et ensuite j'explique son but :

Code :

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

1
2
3
4
5
6
select REPLACE(email_addr,'.','') as "email_a_tester"
from compte
where email_addr like '%@gmail.com' and
email_addr not like ''
group by REPLACE(email_addr,'.','')
HAVING COUNT(email_addr) >= 1

Avec les commentaires:
select REPLACE(email_addr,'.','') as "email_a_tester" // je retire tous les points "." dans les adresses emails en créant une colonne propre avec "as" car sinon ça va créer une colonne avec le nom en "REPLACE(email_addr,'.','')" et c'est pas beau
from compte // la table
where email_addr like '%@gmail.com' and // je récupère seulement emails se terminant par %@gmail.com, pas les autres
email_addr not like '' // je ne récupère pas les quelques comptes créés autrefois avec un numéro de tél (qui sont sans adresse email)
group by REPLACE(email_addr,'.','') // c'est comme le select tout en haut du coup
HAVING COUNT(email_addr) >= 1 // je récupère toute la liste d'email pour les lister dans la colonne "email_a_tester"

Vous l'aurez compris, cette table elle liste toutes les adresses emails venant d'une table en enlevant tous les ".", donc "to.to@gmail.com" s’affichera en "toto@gmailcom".

Maintenant ce que je cherche à faire c'est comment devrais-je faire pour rechercher "toto@gmailcom" à partir de la colonne "email_a_tester" ?

PS: "email_a_tester" c'est un anti-doublons-gmail-avec-ou-sans.+, plus d'informations ici : https://support.google.com/mail/answer/7436150?hl=fr (c'est valable à la fois pour les "." et les "+" visiblement) - Actuellement je teste qu'avec les "." pour faire simple.

Cdt

[escartefigue]

Bonsoir,

De ce que je comprends de votre besoin, il suffit d'encapsuler votre requête dans une autre (utiliser une table dérivée donc) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select [...] 
from ma_table as T1
inner join
     (select REPLACE(email_addr,'.','') as email_a_tester
      from compte
      where email_addr like '%@gmail.com' 
        and email_addr not like ''
      group by REPLACE(email_addr,'.','')
      HAVING COUNT(email_addr) >= 1
     ) as T2
   on T2.email_a_tester=T1.courriel
where [...]

On peut faire la même chose avec EXISTS, IN...

Par contre, comme il existe des adresses valides avec un point avant le @, pourquoi faire cette gymnastique ?

[mecmec]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
select T2.email_a_tester
from compte
inner join
     (select REPLACE(email_addr,'.','') as email_a_tester
      from compte
      where email_addr like '%@gmail.com' 
        and email_addr not like ''
      group by REPLACE(email_addr,'.','')
      HAVING COUNT(email_addr) >= 1
     ) as T2
   on T2.email_a_tester="toto@gmailcom" limit 1

C'est super merci ça fonctionne bien

Par contre, comme il existe des adresses valides avec un point avant le @, pourquoi faire cette gymnastique ?

Imaginons que tu crées un compte gmail et ton adresse principale c'est escartefigue@gmail.com, tu peux être contacter en modifiant l'email escartefigue@gmail.com par des "." ou des "+" (exemple: e.s.c.a.rte.fig.ue@gmail.com) du coup tu vas donc recevoir un email, c'est indiqué sur https://support.google.com/mail/answer/7436150?hl=fr et moi j'veux utiliser cette requête SQL pour lister toutes les adresses emails en retirant tous les "." (et "+" pour plus tard) et il faut donc retirer les "." et "+" de l'email à vérifier :
on T2.email_a_tester="toto@gmailcom" (sans . ni +)

Et cela bloqueras donc les contourneurs qui s’amusent à s'inscrire sur le site en rajoutant des "." dans n'importe quel endroit devant le "@".


Cdt

[Artemus24]

Salut à tous.

Je viens de lire ce document :
--> https://support.google.com/mail/answer/7436150?hl=fr

et je constate que la syntaxe des adresses email est très particulière chez google.
Il y a sûrement des petits malins qui ont abusé des adresses emails en jouant sur les point.

@ escartefigue : votre solution ne peut pas fonctionner car dans la table compte, l'email peut s'écrire de plusieurs façon.
En fait, vous ne récupérez que l'email sous cette forme "toto@gmail.com" et non ceux avec des points "t.o.t.o@gmail.com".

@ tous : la solution est de retourner toutes les lignes répondant aux critères, sans faire de produit cartésien.
Pour ce faire, il faut passer par une vue qui va extraire et mettre en forme toutes les lignes, sans les regrouper.
Puis faire une jointure entre la vue afin de donner les identifiant, et la même vue avec regroupement.
Ce qui donne :

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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
(  `id`      integer unsigned  NOT NULL auto_increment Primary key,
   `email`   varchar(255)      NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `test` (`email`) VALUES
  ('jean.du.pont@gmail.com'),
  ('jean.du.pont@outlook.com'),
  ('j.e.a.n.d.u.p.o.n.t@gmail.com'),
  ('je.an.du.po.nt@gmail.com'),
  ('jean-du-pont@gmail.com'),
 
  ('jean.du.pond@gmail.com'),
  ('jean.du.pond@outlook.com'),
  ('j.e.a.n.d.u.p.o.n.d@gmail.com'),
  ('jea.ndu.po.n.d@gmail.com'),
  ('je.an.du.po.nd@gmail.com'),
  ('jean-du-pond@gmail.com')
--------------
 
--------------
select * from  `test`
--------------
 
+----+-------------------------------+
| id | email                         |
+----+-------------------------------+
|  1 | jean.du.pont@gmail.com        |
|  2 | jean.du.pont@outlook.com      |
|  3 | j.e.a.n.d.u.p.o.n.t@gmail.com |
|  4 | je.an.du.po.nt@gmail.com      |
|  5 | jean-du-pont@gmail.com        |
|  6 | jean.du.pond@gmail.com        |
|  7 | jean.du.pond@outlook.com      |
|  8 | j.e.a.n.d.u.p.o.n.d@gmail.com |
|  9 | jea.ndu.po.n.d@gmail.com      |
| 10 | je.an.du.po.nd@gmail.com      |
| 11 | jean-du-pond@gmail.com        |
+----+-------------------------------+
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
   select  id,
           replace(`email`,'.','') as test
 
     from  `test`
    where  email like '%@gmail.com'
--------------
 
--------------
select * from `vue`
--------------
 
+----+-----------------------+
| id | test                  |
+----+-----------------------+
|  1 | jeandupont@gmailcom   |
|  3 | jeandupont@gmailcom   |
|  4 | jeandupont@gmailcom   |
|  5 | jean-du-pont@gmailcom |
|  6 | jeandupond@gmailcom   |
|  8 | jeandupond@gmailcom   |
|  9 | jeandupond@gmailcom   |
| 10 | jeandupond@gmailcom   |
| 11 | jean-du-pond@gmailcom |
+----+-----------------------+
--------------
select      t1.id,
            t1.test
      from  `vue` as t1
 
inner join (     select  test
                   from  `vue`
               group by  test
                 having  count(test) > 1
           ) as t2
        on  t2.test = t1.test
--------------
 
+----+---------------------+
| id | test                |
+----+---------------------+
|  1 | jeandupont@gmailcom |
|  3 | jeandupont@gmailcom |
|  4 | jeandupont@gmailcom |
|  6 | jeandupond@gmailcom |
|  8 | jeandupond@gmailcom |
|  9 | jeandupond@gmailcom |
| 10 | jeandupond@gmailcom |
+----+---------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Attention à dupont et dupond.

@+