Data products such as the NALENND™ Wireless Telephone Number Identification Database are used to distinguish wireless telephone numbers from landline telephone numbers based on original carrier assignments.
With the introduction of wireless number portability in 2003, these products must be augmented with current ported number lists in order to fully comply with United States TCPA and other Wireless-do-not-call regulations.
The Neustar Intermodal Ported Telephone Number Identification Service provides a listing of nationwide ported telephone numbers that have been moved from wire line to wireless and vice versa, enabling users to avoid using auto-dialers or pre-recorded messages to call wireless numbers.
Distribution Frequency: Daily
Distribution Format: Internet download
Download Size: Approximately 30MB compressed
The Neustar download contains four data files; WIRELESS-TO-WIRELINE.TXT, WIRELESS-TO-WIRELINE-NORANGE.TXT, WIRELINE-TO-WIRELESS.TXT, WIRELINE-TO-WIRELESS-NORANGE.TXT.
The WIRELESS-TO-WIRELINE-NORANGE.TXT and WIRELINE-TO-WIRELESS-NORANGE.TXT are simple ASCII text files. Each row contains a single ten digit United States telephone number terminated with a single (newline) line feed character.
5715559876 5719509876 5719589876 5719589879 5719599876 5719599879
The WIRELESS-TO-WIRELINE.TXT and WIRELINE-TO-WIRELESS.TXT are simple ASCII text files. Each row is terminated with a single (newline) line feed character. Each data field is separated with a single ASCII pipe "|" character.
Each row contains a range of ten digit United States telephone numbers that have been ported. The format is a ten digit telephone number followed by the four digit station range.
5715559876|| 5719501010|1014| 5719509876|| 5719589876|| 5719589879|| 5719599876|| 5719599879|| 5719599890|9899|
Determining whether a telephone number is wireless or landline for United States TCPA and other Wireless-do-not-call regulations, requires both the Neustar Intermodal Ported Telephone Number Identification Service data and a data product such as the NALENND™ Wireless Telephone Number Identification Database.
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. 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 `nalennd` 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 `WIRELINE-TO-WIRELESS` 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 `WIRELESS-TO-WIRELINE` 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