I have list of huge phone numbers to be imported to the database. And want to group each phone number by their states and timezones of the state, by identifying them from their prefix. Timezones are Eastern, Central, Pacific etc .. For example 786800XXXX is a number from Florida which is in Eastern timezone. I thought of doing this via a trigger where I check the first three digits of each number and then put them to matched state. Something like SELECT LEFT(NEW.contact , 3) INTO vAreaCode;
and then
SET NEW.timezone = vTimezone;
This probably is going to be resource hog on a busy server. What would be the best way of achieving this task?
The reference table look like
CREATE TABLE `timezones` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`prefix` varchar(11) DEFAULT NULL,
`state` varchar(11) DEFAULT NULL,
`timezone` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
id prefix state timezone
1 786 Florida EDT
2 213 California PST
And my contacts table look like
CREATE TABLE `contacts` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`contact` varchar(11) DEFAULT NULL,
`timezone` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
id contact timezone
1 2147483647 EDT
2 2248918882 CST