0

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

0

DRY -- a database principle -- "Don't Repeat Yourself".

Instead of copy the data over as you load it, look it up when you need it.

SELECT c.contact, tz.state, tz.timezone
    FROM contacts AS c
    JOIN timezones AS tz  ON tx.prefix = LEFT(c.contact, 3)

Caveat: This assumes that you won't modify the timezone after assigning it to a contact.

What is the application? Perhaps some form of call center?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.