Prefix matching while importing large CSV file 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
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?