Update table to set country (LAND in example) based on e-mail endings.

[henrik1782]

I use this MySQL list to update the Country (LAND) based on e-mail endings, but it will search the entire table each time. Isen't there a more effective way to do this?

UPDATE `Bruger_data` SET `LAND` = "Denmark" WHERE `EMAIL` LIKE '%.dk' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Faroe Islands" WHERE `EMAIL` LIKE '%.fo' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Greenland" WHERE `EMAIL` LIKE '%.gl' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Norway" WHERE `EMAIL` LIKE '%.no' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Sweden" WHERE `EMAIL` LIKE '%.se' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Finland" WHERE `EMAIL` LIKE '%.fi' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Argentina" WHERE `EMAIL` LIKE '%.ar' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "France" WHERE `EMAIL` LIKE '%.fr' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "India" WHERE `EMAIL` LIKE '%.in' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Netherlands" WHERE `EMAIL` LIKE '%.nl' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Taiwan" WHERE `EMAIL` LIKE '%.tw' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Poland" WHERE `EMAIL` LIKE '%.pl' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Czech Republic" WHERE `EMAIL` LIKE '%.cz' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Latvia" WHERE `EMAIL` LIKE '%.lv' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "South Africa" WHERE `EMAIL` LIKE '%.za' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Australia" WHERE `EMAIL` LIKE '%.au' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Great Britain" WHERE `EMAIL` LIKE '%.co.uk' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Belgium" WHERE `EMAIL` LIKE '%.be' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "New Zealand" WHERE `EMAIL` LIKE '%.co.nz' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Slovenia" WHERE `EMAIL` LIKE '%.si' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Brazil" WHERE `EMAIL` LIKE '%.com.br' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Israel" WHERE `EMAIL` LIKE '%.co.il' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Switzerland" WHERE `EMAIL` LIKE '%.ch' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Ireland" WHERE `EMAIL` LIKE '%.ie' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Canada" WHERE `EMAIL` LIKE '%.ca' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Hungary" WHERE `EMAIL` LIKE '%.hu' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Russia" WHERE `EMAIL` LIKE '%.ru' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Spain" WHERE `EMAIL` LIKE '%.es' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Slovakia" WHERE `EMAIL` LIKE '%.sk' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Bulgaria" WHERE `EMAIL` LIKE '%.bg' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Italy" WHERE `EMAIL` LIKE '%.it' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Germany" WHERE `EMAIL` LIKE '%.de' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "China" WHERE `EMAIL` LIKE '%.cn' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Austria" WHERE `EMAIL` LIKE '%.at' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Cyprus" WHERE `EMAIL` LIKE '%.cy' AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Denmark" WHERE `SPROG` = "DA" AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Norway" WHERE `SPROG` = "NO" AND `LAND` = "Not set";
UPDATE `Bruger_data` SET `LAND` = "Sweden" WHERE `SPROG` = "SV" AND `LAND` = "Not set";

 

[feherke]

Hi

Isen't there a more effective way to do this?

Sorry, but almost any way would be more efficient than what you posted.

Put all those country names and CCTLD's in a separate table :

[b]create[/b] [b]table[/b] country [teal]([/teal]
  id integer [b]primary[/b] [b]key[/b] [b]auto_increment[/b][teal],[/teal]
  name [maroon]varchar[/maroon][teal]([/teal][purple]25[/purple][teal]),[/teal]
  cctld [maroon]varchar[/maroon][teal]([/teal][purple]2[/purple][teal])[/teal]
[teal]);[/teal]

[b]insert[/b] [b]into[/b] country [teal]([/teal]name[teal],[/teal]cctld[teal])[/teal] [b]values[/b]
[teal]([/teal][green][i]'Denmark'[/i][/green][teal],[/teal][green][i]'dk'[/i][/green][teal]),[/teal]
[teal]([/teal][green][i]'Faroe Islands'[/i][/green][teal],[/teal][green][i]'fo'[/i][/green][teal]),[/teal]
[teal]([/teal][green][i]'Greenland'[/i][/green][teal],[/teal][green][i]'gl'[/i][/green][teal]);[/teal]

[b]update[/b] bruger_data[teal],[/teal]country

[b]set[/b] land[teal]=[/teal]name

[b]where[/b] email [b]like[/b] concat[teal]([/teal][green][i]'%.'[/i][/green][teal],[/teal]cctld[teal])[/teal]
[b]and[/b] land[teal]=[/teal][green][i]'not set'[/i][/green][teal];[/teal]

Not sure what sprog is, but probably requires a very similar [tt]update[/tt] statement.


Feherke.

http://feherke.github.com/

 

[r937]

nice one, fehereke

i was going to suggest exactly the same solution but you beat me to it

note: cctld should be more than 2 characters to handle domains like .co.uk

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[henrik1782]

Thanks a lot.

I really appreciate and will give it a shot when I get home.

Best regards
Henrik

 

[feherke]

Hi

note: cctld should be more than 2 characters to handle domains like .co.uk

Not sure. I just supposed while .co.uk is being associated with Great Britain, probably .org.uk, .net.uk and the others will also associated with Great Britain. I mean, there is no sign the second level domains would be reflected by any appendices after the country name, or anything similar. So I supposed there will be a single record with name 'Great Britain' and cctld 'uk'. And for that 2 characters should be enough :

All ASCII ccTLD identifiers are two letters long, and all two-letter top-level domains are ccTLDs.

( Country code top-level domain article on Wikipedia. )

Henrik, one more thing. Storing the country names in each record is against the normalization rule. Especially if you take my advice and use a country table. Usually just the country.id should be stored in the bruger_data table.

Feherke.

http://feherke.github.com/

 

[henrik1782]

Thanks for all your help. It just worked like a charm.

Found the list with all cctld's

http://www.iana.org/domains/root/db/

and this actually requere more than 2 chars to put in all, but for my needs it is ok.

 

[Olavxxx]

Maybe you should consider using a iso3166 table with country codes.
eg. 3 char

2 char | 3 char | full
NO | NOR | NORWAY

Look here:

http://www.iso.org/iso/country_codes/iso_3166_code_lists.htm

Ps. still you have a problem, I for instance use .com emails, I use .net e-mails and .no You would update me as beeing american, based on the gmail adress I have.

So if you wish to auto-update, I think I'd rather use the IP and geolocation database.

http://www.maxmind.com/app/geolitecity

Still not perfect, but I think you can trust geolocation database more than the TLD of the e-mail.

Olav Alexander Mjelde