The NALENND™ Wireless Telephone Number Identification Database is a wireless block identifier database used to identify whether a United States or a Canadian telephone number is wireless (cellular) or landline based on current telecom carrier block assignments.
Updated monthly, the database lists all active telephone Area Codes (NPA) and Exchange Prefixes (NXX) at the thousands block level with each block representing 1,000 telephone numbers.
This data provides a first step for those applications seeking U.S. FCC TCPA compliancy. These wireless do not call rules prohibit the initiation of telephone calls using automatic telephone dialing systems or an artificial or prerecorded voice to telephone numbers assigned to a paging service, cellular telephone service, specialized mobile radio service, or other radio common carrier service, or any service for which the called party is charged for the call.
While this database can correctly identify approximately 97% of all telephone numbers as either landline or wireless, both the NALENND™ wireless block identifier database and the Neustar Intermodal Ported Telephone Number database which contains United States nationwide ported telephone numbers that have been moved from landline to wireless and wireless to landline are required for those applications seeking 100% compliance with United States wireless do not call rules.
Produced: on or before 5th calendar day monthly
Current Release: February 2, 2017
Distribution Frequency: monthly
Distribution Format: Internet download
|Data Field||Premium Edition||Standard Edition|
|Area Code (NPA)||Yes||Yes|
|Exchange Prefix (NXX)||Yes||Yes|
|1,000 Block Identifier||Yes||Yes|
|ISO 3166 Country Code||Yes||No|
|State or Province Abbreviation||Yes||No|
|Time Zone (UTC)||Yes||No|
|Daylight Savings Time (DST)||Yes||No|
|Number Type (BLOCKTYPE)||Yes||Yes|
The Premium Edition database provides the information required to determine if a United States or Canadian telephone number is wireless or landline based on original carrier allocation. This version additionally provides information to determine the time zone based on the geographic area associated with the telephone number.
CREATE DATABASE if not exists `nalennd`; USE `nalennd`; DROP TABLE IF EXISTS `tcpa`; CREATE TABLE `tcpa` ( `NPA` CHAR(3) NOT NULL, `NXX` CHAR(3) NOT NULL, `BLOCK` CHAR(1) NOT NULL, `COUNTRY` CHAR(2) NOT NULL, `STATE` CHAR(2) NOT NULL, `UTC` CHAR(6) NOT NULL, `DST` CHAR(1) NOT NULL, `BLOCKTYPE` CHAR(1) NOT NULL, `RESERVED` VARCHAR(128) DEFAULT NULL, PRIMARY KEY (`NPA`,`NXX`,`BLOCK`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;Sample Data
The Standard Edition database provides the minimum information required to determine if a United States or Canadian telephone number is wireless or landline based on original carrier allocation.
CREATE DATABASE if not exists `nalennd`; USE `nalennd`; DROP TABLE IF EXISTS `tcpa`; CREATE TABLE `tcpa` ( `NPA` CHAR(3) NOT NULL, `NXX` CHAR(3) NOT NULL, `BLOCK` CHAR(1) NOT NULL, `BLOCKTYPE` CHAR(1) NOT NULL, `RESERVED` VARCHAR(128) DEFAULT NULL, PRIMARY KEY (`NPA`,`NXX`,`BLOCK`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;Sample Data
Determining whether a telephone number is wireless or landline is accomplished through a simple SQL query to the NALENND™ data table. Assuming we have the telephone number (352) 357-1234 we would issue the following query. Upon success we would then retrieve the line type from the result set.
SELECT BLOCKTYPE FROM tcpa WHERE NPA='352' AND NXX='357' AND BLOCK='1' LIMIT 1;
While the NALENND™ Wireless Telephone Number Identification Database can identify approximately 97% of all United States telephone numbers as wireless or landline, for full TCPA Wireless-do-not-call compliancy the NALENND™ must be augmented with the Neustar Intermodal Ported Telephone Number data. Thus if we have loaded both the NALENND™ and the Neustar data in to our database we could use a simple SQL stored procedure to query the data.
Note this particular procedure is written for MySQL, implementing in other SQL platforms will be similar.
CREATE DEFINER=`root`@`%` PROCEDURE `linetype`(phone CHAR(10)) READS SQL DATA BEGIN DECLARE ltype CHAR(1) DEFAULT null; DECLARE ported CHAR(1) DEFAULT 'N'; SELECT `btype` INTO ltype FROM `tcpa` WHERE `NPA`=SUBSTRING(phone,1,3) AND `NXX`=SUBSTRING(phone,4,3) AND `BLOCK`=SUBSTRING(phone,7,1) LIMIT 1; IF (ltype IS NOT NULL) THEN /* we have found the number block in our NALENND data, check Neustar to see if ported */ IF (ltype = 'S') THEN IF EXISTS(SELECT 1 FROM `landline2wireless` WHERE `number`=phone LIMIT 1) THEN /* number has been ported, was originally a landline */ SET ported = 'Y'; SET ltype = 'W'; ELSE SET ltype = 'L'; END IF; ELSE IF EXISTS(SELECT 1 FROM `wireless2landline` WHERE `number`=phone LIMIT 1) THEN /* number has been ported, was originally wireless */ SET ported = 'Y'; SET ltype = 'L'; ELSE SET ltype = 'W'; END IF; END IF; END IF; SELECT ltype,ported; END
To use the stored procedure, again using the telephone number (352) 357-1234, we would issue our SQL query like so.
We would then retrieve the ltype and ported values from our result set.