Home Forums SQL Server 2008 SQL Server Newbies Sql Query to find invalid SSN Numbers RE: Sql Query to find invalid SSN Numbers

  • March 15, 2019 at 7:03 am

    #2024558

    Sean Lange - Tuesday, March 12, 2019 8:33 AM

    glenn.rollins3 - Tuesday, March 12, 2019 8:02 AM

    I've been using the following and it's proved successful.

    WHERE LEN(SSN) <> 9
     OR LEFT(SSN,1) IN ('8')
     OR LEFT(SSN,2) IN ('74','78','79')
     OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
     OR LEFT(SSN,3) = '000'
     OR SUBSTRING(SSN,4,2) = '00'
     OR SUBSTRING(SSN,6,4) = '0000'
     --OR SSN IS NULL
     OR SSN LIKE '%[a-z]%'

    Thoughts?

    I think that storing SSN in plain text is criminal behavior.

    It still happens, I seem to remember supplying my SSN all over the place and could not avoid it if I didn't want to live like a hermit in a cave 😉 This means its subsequently shared with upstream servicers, etc, and that means its going to be processed, oftentimes as a lookup / matching key. This means storage, transmission, matching etc, so in my opinion its going to be a hard sale on trying to get routine processing of this data "criminalized".