NALENND^®
Telephone Number Type Identifier Database

The Gold 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. For United States thousands block entries the Gold Edition includes the count of landline to wireless and wireless to landline number ports for the block

SQL Table Definition

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,
	`L2W_PORTS` INT(11) NOT NULL,
	`W2L_PORTS` INT(11) NOT NULL,
	`OCN` char(4) NOT NULL,
	`COMPANY` varchar(128) default NULL,
	PRIMARY KEY (`NPA`,`NXX`,`BLOCK`)
	)
ENGINE=MyISAM DEFAULT CHARSET=utf8;

 Sample Data
 Reference Manual

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.

SQL Table Definition

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
 Reference Manual

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.

SQL Table Definition

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
 Reference Manual

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^® Telephone Number Type Identifier 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 iconnectv Intermodal Ported Telephone Number data. Thus if we have loaded both the NALENND^® and the iconnectv 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 iconnectv 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.

CALL linetype('3523571234');

We would then retrieve the ltype and ported values from our result set.