Spanish Fiscal Identification Number and Foreign Identification Number masking

You can use the TRANS NID function to mask Spanish Fiscal Identification Numbers (NIF) and Foreign Identification Numbers (NIE).

An NIF is an eight character value in the following format NNNNNNN-A, where the first seven characters are a serial number and the final character is an alphabetic suffix. The suffix is a check digit.

Foreign Spanish nationals use a Foreign Identification Number (NIE), which is a nine character value that uses the same format as an NIF, but is preceded by an X. An NIE uses the following format: X-NNNNNNN-A.

The TRANS NID function generates a masked NIF or NIE with a check digit that is calculated based on the preceding masked 7 digits of the output value.

Output formats (FMT=)

The following output formats are available for an NIF and NIE.

For each format, all characters are masked. NIF and NIE numbers use the same format options. An NIE source value will always include an X prefix in the output value.

Fields to be masked Format without separator Format with dash separator Format with space separator
Serial, Suffix (MTD=mask default) ES=8X ES=7X-1X ES=7X 1X

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 8 characters.
VARCHAR and NVARCHAR
The length of the column must be at least 8 characters.

Validation (VAL=Y)

If the VAL=Y parameter is used, a source row will be skipped if it contains an invalid suffix.

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:

Invalid length
The input value does not contain separators and is less than 8 characters (NIF) or is less than 9 characters (NIE).
The input value contains separators and is less than 9 characters (NIF) or is less than 11 characters (NIE).
Invalid pattern
The length of the source value is valid, but the value does not match the pattern of an NIF or NIE.
Separators
Different separators were found in positions 2 and 10 in an NIE source value.
The source value contains an invalid separator.
Null value
The source value is NULL but the destination column does not allow a NULL value.


Feedback