[MySql] Alle e-mail addressen vinden.

[Vincenz0]

Dag mede codekloppers.

voor mijn stage-opdracht zit ik met het volgende:

Ik heb een db gekregen die bestaat uit 3 tabellen met verschillende klant gegevens.
Deze tabellen bestaan voor het grootste gedeelte uit varchar velden die in de loop der tijd door de gebruikers zijn ingevuld.
Het nadeel hieraan is dat het dus ook wel eens foutief word ingevuld, bijvoorbeeld een e-mail adres staat in het veld voor het klantnummer.

een paar voorbeeld regels van de tabel: (normaal bevat hij iets van 30 kolommen)

code:

tabel1
kolom1   kolom2   kolom3            kolom4           kolom5
1        bedrijf  [e-mail]   [e-mail]   Langeweg 21
2        client   [e-mail]     C28632           onbekend

Nu is het de bedoeling dat ik een lijst krijg met alle unieke e-mail adressen die in de verschillende tabellen/kolommen staan.

bijvoorbeeld:

code:

email
[e-mail]
[e-mail]
[e-mail]

Nu heb ik zelf zo iets bedacht:

SQL:

select (Case when kolom1 like '%@%' 
        then Kolom1 
        else (Case when kolom2 like '%@%'
              then kolom2 
              else (Case when kolom3 like '%@%'
                    then kolom3
                    else null
                    end)
              end)
        end) as email
from tabel1

Aan deze oplossing kleven 2 nadelen.
Ten eerste moet ik alle kolommen/tabellen gaan benoemen. en dat zijn er nogal wat.
ten 2e krijg ik natuurlijk nu maar per record in de table 1 maal een e-mail adres terug. bijv:

code:

email
[e-mail]
[e-mail]

Mocht je een geniaal idee hebben, roept u maar.


[edit]
oplossing:

SQL:

select distinct email from
(
    (select kolom1 as email from tabel1 where kolom1 like '%@%.%')
    union all
    (select kolom2 as email from tabel1 where kolom2 like '%@%.%')
    union all
    (select kolom1 as email from tabel2 where kolom1 like '%@%.%')
    union all
    (select kolom2 as email from tabel2 where kolom2 like '%@%.%')
    union all
    (select kolom3 as email from tabel2 where kolom3 like '%@%.%')
    union all
    (select kolom4 as email from tabel2 where kolom4 like '%@%.%')
    order by email asc
) as emaillist

[ Voor 15% gewijzigd door Vincenz0 op 03-03-2009 14:46 ]

[Luqq]

Schiet me zo niks te binnen, maar lekkere DB layout heb je als ze email adressen bij een klantnummer kunnen invullen..

[Vincenz0]

Luqq schreef op dinsdag 03 maart 2009 @ 12:59:
Schiet me zo niks te binnen, maar lekkere DB layout heb je als ze email adressen bij een klantnummer kunnen invullen..

100% mee eens, die db komt van een extern bedrijf en ik ben dus een nieuw programma aan het ontwikkelen en moet onderandere verschillende brokken oude data gebruiken

[ Voor 4% gewijzigd door Vincenz0 op 03-03-2009 13:01 ]

[Voutloos]

UNION ALL per kolom, in elke subquery naar dezelfde alias hernoemen en dan over de totale query een distinct.

(Maar dan nog is het datamodel inderdaad een enorme WTF)

[ Voor 20% gewijzigd door Voutloos op 03-03-2009 13:03 ]

[Mr_Light]

Ik zou gewoon alles naar binnen trekken (of mischien iets minder bruut en in stukken)(en/of met heuristieken: (alleen dingen welke een '@' bevatten binnenhalen))

en dan vervolgens stukje code schrijven en de informatie er uit halen. Je zal ook moeten kijken of domein namen wel bestaan of iets in die trant.

Daarna zou ik de db admin met een rubberen kip een paar meppen geven.

[ari]

Je kunt een script schrijven dat alle tabellen in je db uitleest, en daarna alle kolommen per gegeven tabel. Vervolgens voer je dan per keer een query uit om uit die kolom alle e-mailadressen te pakken.

Dan heb je alle adressen uit je db, dan hoef je ze alleen nog maar te filteren.

[Vincenz0]

Ik heb het liefste een MySQL-only oplossing ivm optimalisatie, mocht dat niet lukken dan kan ik nog wel kijken of ik het aan de client kant opgelost kan worden.

@ Voutloos, hmm das een goede, alleen moet nog steeds alle kolommen/tabbellen bij naam benoemen. Toch even proberen.

[ Voor 27% gewijzigd door Vincenz0 op 03-03-2009 13:10 ]

[JHMvP]

Als ik nou een stukje HTML heb waarmee je alle emailadressen uit een tekstvak haalt...kun je gelijk aan de slag! Is wel geen SQL en ik weet niet of dat zomaar mag...

[Voutloos]

De sql query die ik voorstel heb je in max 5 minuten geschreven?

[Verwijderd]

Hieronder vind je iets om door je kolommen te lopen van je tabel (gebruik maken van de systeemtabellen) ...

Het is wel voor SQL-server van MS, maar MYSQL heeft dat ook dachtek ...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70539

[Vincenz0]

Ik heb het maar gedaan op de manier die Voutloos aangeeft:

SQL:

select distinct email from
(
    (select kolom1 as email from tabel1 where kolom1 like '%@%.%')
    union all
    (select kolom2 as email from tabel1 where kolom2 like '%@%.%')
    union all
    (select kolom1 as email from tabel2 where kolom1 like '%@%.%')
    union all
    (select kolom2 as email from tabel2 where kolom2 like '%@%.%')
    union all
    (select kolom3 as email from tabel2 where kolom3 like '%@%.%')
    union all
    (select kolom4 as email from tabel2 where kolom4 like '%@%.%')
    order by email asc
) as emaillist

Het controleren of de mail adressen valide zijn gebeurt al in de code.
Bedankt voor de hulp jongens.

[ Voor 5% gewijzigd door Vincenz0 op 03-03-2009 14:44 ]

[DamadmOO]

Voutloos schreef op dinsdag 03 maart 2009 @ 13:03:
UNION ALL per kolom, in elke subquery naar dezelfde alias hernoemen en dan over de totale query een distinct.

(Maar dan nog is het datamodel inderdaad een enorme WTF)

Waarom UNION ALL icm distinct. Gebruik dan gewoon een normale union en dubbele waardes worden er al uit gefilterd

[Voutloos]

Hmz klopt. Is een dwangneurose van mij, als optimalisatie tov UNION welke default distinct is. Lees: ik schrijf automagisch all tenzij het niet juist is.

(Niet gebenchmarked, maar het is zelfs mogelijk dat allemaal union alls en dan daarover een distinct sneller is. Maar meten is weten, triviale optimalisatie, of wellicht wel erg micro/prematuur)

[ Voor 38% gewijzigd door Voutloos op 03-03-2009 15:24 ]