1 post / 0 new
Finding Cell Phones -- maybe...

Hello everyone, 

During a meeting a question came up about any possible way to determine which phone numbers in our address table might be cell phones versus numbers that are land-lines. I gave it a little bit of thought and figured one possible way is to compare the area codes of the phone numbers we have for people versus the indigenous area codes of where they currently live. The logic ran that if someone purchased a cell phone in one area code and moved, their area code on their cell phone would not match the area code serviced by the zip code they currently inhabit. These mismatches might be cell phones. I figured this kind of thing might be useful if anyone else ran into this question.

Free online database that correlates zip codes and area codes:   https://www.unitedstateszipcodes.org/zip-code-database/

View for Good Valid Preferred addresses that I used:


CREATE VIEW [dbo].[rpt_addressGoodValPref]



Select * from address

where       addrlocatr = 'g'

and addrmc1 = 'Y'

        and Case month( getdate()) 

WHEN 1 THEN addrjan

WHEN 2 THEN addrfeb

WHEN 3 THEN addrmar

WHEN 4 THEN addrapr

WHEN 5 THEN addrmay

WHEN 6 THEN addrjun

WHEN 7 THEN addrjul

WHEN 8 THEN addraug

WHEN 9 THEN addrsep

WHEN 10 THEN addroct

WHEN 11 THEN addrnov

WHEN 12 THEN addrdec

ELSE addrdec END = 'Y'


Import the zip_code_database.csv file from the website into a table, I called mine ajm_areacode. 

T-SQL script:

select o.* from (

select addrid,addrphone,

substring(ltrim(addrzipcod),0,6) as zip_m,

substring(ltrim(addrphone),0,4) as ac_m,


from rpt_addressGoodValPref join ajm_areacode on substring(ltrim(addrzipcod),0,6)=zip

where CHARINDEX(substring(ltrim(addrphone),0,4),area_codes)=’0′) o

where o.ac_m is not null and o.ac_m!=”

It's not pretty, but it works. :) 


YMMV. :) 

Andy McHugh

Western Michigan University


Work: 269-387-8719

GV: 269-216-4597

Current Config: Mill 7.9.1, SQL2k8r2, IIS 6.0, Windows 2003 Servers, 344k corebio.