French National Institute for Statistics and Economic Studies Number masking

You can use the TRANS NID function to mask French National Institute for Statistics and Economic Studies numbers (INSEE).

An INSEE number is a 15-digit number with the following format: SYYMMDDCCCOOOKK.
S
Sex and citizenship information.
YY
Last two digits of the year of birth.
MM
Month of birth.
DD
Department of origin.
CCC
Commune of origin.
OOO
Order number.
KK
Control key or check digits.

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

Output formats (FMT=)

The following output formats are available for an INSEE.

All formats mask the order and check digit fields. If the department field is masked, the commune field will also be masked with a compatible value.

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 (in addition to Order and Check Digit) Format without separator Format with dash separator Format with space separator
Sex, Year, Month, Commune (MTD=mask default) FR=5X2C8X FR=5X2C6X-2X FR=5X2C6X 2X
Sex FR=1X9C5X FR=1X9C3X-2X FR=1X9C3X 2X
Sex, Year FR=3X7C5X FR=3X7C3X-2X FR=3X7C3X 2X
Sex, Month FR=1X2C2X5C5X FR=1X2C2X5C3X-2X FR=1X2C2X5C3X 2X
Sex, Commune FR=1X6C8X FR=1X6C6X-2X FR=1X6C6X 2X
Sex, Department FR=1X4C8X FR=1X4C6X-2X FR=1X4C6X 2X
Sex, Year, Month FR=5X5C5X FR=5X5C3X-2X FR=5X5C3X 2X
Sex, Year, Commune FR=3X4C8X FR=3X4C6X-2X FR=3X4C6X 2X
Sex, Year, Department, Commune FR=3X2C10X FR=3X2C8X-2X FR=3X2C8X 2X
Sex, Month, Commune FR=1X2C2X2C8X FR=1X2C2X2C6X-2X FR=1X2C2X2C6X 2X
Sex, Month, Department, Commune FR=1X2C12X FR=1X2C10X-2X FR=1X2C10X 2X
Sex, Year, Month, Department, Commune FR=15X FR=13X-2X 13X FR=13X 2X
Year FR=1C2X7C5X FR=1C2X7C3X-2X FR=1C2X7C3X 2X
Year, Month FR=1C4X5C5X FR=1C4X5C3X-2X FR=1C4X5C3X 2X
Year, Commune FR=1C2X4C8X FR=1C2X4C6X-2X FR=1C2X4C6X 2X
Year, Department FR=1C2X2C10X FR=1C2X2C8X-2X FR=1C2X2C8X 2X
Year, Month, Commune FR=1C4X2C8X FR=1C4X2C6X-2X FR=1C4X2C6X 2X
Year, Month, Department FR=1C14X FR=1C12X-2X FR=1C12X 2X
Month FR=3C2X5C5X FR=3C2X5C3X-2X FR=3C2X5C3X 2X
Month, Commune FR=3C2X2C8X FR=3C2X2C6X-2X FR=3C2X2C6X 2X
Month, Department FR=3C12X FR=3C10X-2X FR=3C10X 2X
Commune FR=7C8X FR=7C6X-2X FR=7C6X 2X
Department FR=5C10X FR=5C8X-2X FR=5C8X 2X

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 15 characters.
VARCHAR and NVARCHAR
The length of the column must be at least 15 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 commune field value is invalid.
  • The source check digit field 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 source value includes the following errors:

Null value
The source value is NULL but the destination column does not allow a NULL value.
Invalid INSEE
  • The size of the input INSEE value is more or less than valid.
  • The input INSEE value contains invalid separators or separators in the wrong position.
  • The sex field is not one of the following values: 1, 2, 7, or 8.


Feedback