Canadian Social Insurance Number masking

You can use the TRANS NID function to mask Canadian Social Insurance Numbers (SIN).

An SIN is a nine-digit number that consists of a one-digit region code number followed by an eight-digit serial number. The first three digits are called the header. The last digit of the serial number is a check digit.

The TRANS NID function generates a masked SIN with a check digit that is calculated based on the preceding masked eight digits of the output value.

Output formats (FMT=)

The following output formats are available for an SIN.

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
Serial number without header digits (MTD=mask default) CA=3C6X CA=3C-3X-3X CA=3C 3X 3X CA=3C.3X.3X
Serial number and header digits CA=9X CA=3X-3X-3X CA=3X 3X 3X CA=3X.3X.3X

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 first digit is eight.
  • There are three consecutive zeros at positions 1-3, 4-6, or 7-9.
  • The check digit is invalid.

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 SIN
  • The source column is a CHAR, NCHAR, VARCHAR, or NVARCHAR but the source value is less than 9 characters (not including separators).
  • The source column is a CHAR, NCHAR, VARCHAR, or NVARCHAR but the source value is more than 9 characters (not including separators).
  • The source value includes a non-numeric character.


Feedback