United States Social Security Number masking

You can use the TRANS NID function to mask United States Social Security Numbers (SSN).

An SSN consists of 3 subfields with the following format: AAAGGSSSS.

AAA
Area number. The area is generally determined by the state in which the SSN is issued.
GG
Group number. A group number is assigned based on the area number.
SSSS
Serial number.

The TRANS NID function generates a masked SSN according to the following rules:

Output formats (FMT=)

The following output formats are available for an SSN.

C indicates values that are copied. X indicates values that are masked. For example, 3C4X indicates that the first three characters are copied and the next four characters are masked.

Fields to be masked Format without separator Format with dash separator Format with space separator Format with period separator
Group, Serial number (MTD=mask default) US=3C6X US=3C-2X-4X US=3C 2X 4X US=3C.2X.4X
Area, Group, Serial number US=9X US=3X-2X-4X US=3X 2X 4X US=3X.2X.4X

Data types

The following data types are permitted for source and destination columns. Restrictions for each data type are noted.

CHAR and NCHAR
The length of the column must be at least 9 characters.
DECIMAL
The precision of the column must be between 9 and 20 and the scale must be 0.
INTEGER
No restrictions.
VARCHAR and NVARCHAR
The length of the column must be at least 9 characters.

Validation (VAL=Y)

If the VAL=Y parameter is used, a source row will be skipped if any of the following apply:
  • The source area number exceeds the maximum value.
  • The source area number has not been used by the Social Security Administration.
  • The source group number has not been used for the source area number.

Special processing

The following checks will be made during processing:

Skipped rows

A source row will be skipped and not written to the destination table if the following occurs:

Null value
The source value is NULL but the destination column does not allow a NULL value.
Invalid SSN
  • The source column is a CHAR, NCHAR, VARCHAR, or NVARCHAR, but the source value is more or less than 9 characters (not including separators).
  • The source value includes a non-numeric character.
  • The source value is 0 or a reserved value such as 078-05-1120 and 457-55-5462.


Feedback