Anonymous

		Update templist_{ID}
			Set number = 
				            Replace(
					     Replace(
					      Replace(
					       Replace(
						Replace(
						  Replace(
						    Replace(
						      Replace(
							number,
							' ',
							''
						      	), ')',
							''
						      	), '(',
							''
						   	), '-', 
							''
						  	), '[',
							''
							), ']',
							''
							), '.',
							''
							), ',',
							''
							);

		Update templist_{ID}
			Set number = substring(number, 2, length(number))
		Where number like '1%';
		
		Delete from templist_{ID} where round(number) = 0;
		
		Delete from templist_{ID} where length(number) > 10;
		
		Delete from templist_{ID} where number is Null or number = '';


		drop table if exists templist_{ID}_tmp;

		create table `templist_{ID}_tmp` (`number` bigint default NULL) ENGINE=MEMORY DEFAULT CHARSET=latin1;

		insert into templist_{ID}_tmp (number)
			select number from templist_{ID}
			order by number;

		delete from templist_{ID};

		Alter table templist_{ID} 
			add column npa smallint(3) zerofill default NULL,
			add column nxx smallint(3) zerofill default NULL,
			add column nnnn smallint(4) zerofill default NULL,
			add column loc varchar(10) default NULL,
			add column state varchar(2) default NULL,
			add column ocn varchar(5) default NULL,
			add column lata mediumint(5) zerofill default NULL,
			add column tier varchar(20) default NULL,
			add column rate decimal(6,4) default NULL;

		ALTER TABLE templist_{ID} MODIFY number BIGINT NOT NULL;

		insert into templist_{ID} (number)
			select number from templist_{ID}_tmp;

		drop table templist_{ID}_tmp;

		Update templist_{ID}
		Set 	npa 	= substring(number, 1, 3), 
			nxx 	= substring(number, 4, 3),
			nnnn 	= substring(number, 7, length(number));

		Alter table templist_{ID} 
			ADD INDEX (`npa`), ADD INDEX (`nxx`), ADD INDEX (`nnnn`), ADD INDEX(`number`);

		ANALYZE table templist_{ID};

		Update templist_{ID} 
			Set lata = 
				(Select l.lata 
					from lerg l Where 
						l.npa = templist_{ID}.npa and l.nxx = templist_{ID}.nxx
						and templist_{ID}.nnnn between l.linefr and l.lineto
						and ((l.lineto - templist_{ID}.nnnn) + (templist_{ID}.nnnn - l.linefr)) = 
						(select ceiling(lg.lineto - templist_{ID}.nnnn) + ceiling(templist_{ID}.nnnn - lg.linefr) from 
							lerg lg where 
							lg.nxx = templist_{ID}.nxx and lg.npa = templist_{ID}.npa
							and  templist_{ID}.nnnn between lg.linefr and lg.lineto
							limit 1
						)
				),
			ocn = 
				(Select l.ocn 
					from lerg l Where 
						l.npa = templist_{ID}.npa and l.nxx = templist_{ID}.nxx
						and templist_{ID}.nnnn between l.linefr and l.lineto
						and ((l.lineto - templist_{ID}.nnnn) + (templist_{ID}.nnnn - l.linefr)) = 
						(select ceiling(lg.lineto - templist_{ID}.nnnn) + ceiling(templist_{ID}.nnnn - lg.linefr) from 
							lerg lg where 
							lg.nxx = templist_{ID}.nxx and lg.npa = templist_{ID}.npa
							and templist_{ID}.nnnn between lg.linefr and lg.lineto
							limit 1
						)
				),
			loc = 
				(Select l.locname 
					from lerg l Where 
						l.npa = templist_{ID}.npa and l.nxx = templist_{ID}.nxx
						and templist_{ID}.nnnn between l.linefr and l.lineto
						and ((l.lineto - templist_{ID}.nnnn) + (templist_{ID}.nnnn - l.linefr)) = 
						(select ceiling(lg.lineto - templist_{ID}.nnnn) + ceiling(templist_{ID}.nnnn - lg.linefr) from 
							lerg lg where 
							lg.nxx = templist_{ID}.nxx and lg.npa = templist_{ID}.npa
							and templist_{ID}.nnnn between lg.linefr and lg.lineto
							limit 1
						)
				),
			state = 
				(Select l.locstate
					from lerg l Where 
						l.npa = templist_{ID}.npa and l.nxx = templist_{ID}.nxx
						and templist_{ID}.nnnn between l.linefr and l.lineto
						and ((l.lineto - templist_{ID}.nnnn) + (templist_{ID}.nnnn - l.linefr)) = 
						(select ceiling(lg.lineto - templist_{ID}.nnnn) + ceiling(templist_{ID}.nnnn - lg.linefr) from 
							lerg lg where 
							lg.nxx = templist_{ID}.nxx and lg.npa = templist_{ID}.npa
							and templist_{ID}.nnnn between lg.linefr and lg.lineto
							limit 1
						)
				);

		Update templist_{ID}
			Set rate = (Select rate 
					from x5 Where
						templist_{ID}.npa = x5.npa
						and templist_{ID}.nxx = x5.nxx
					Limit 1
				);