Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

AES_ENCRYPT

AES_ENCRYPT

Returns data in encrypted format. The
PowerCenter Integration Service
uses Advanced Encryption Standard (AES) algorithm with 128-bit encoding. The AES algorithm is a FIPS-approved cryptographic algorithm.
Use this function to prevent sensitive data from being visible to everyone. For example, to store social security numbers in a data warehouse, use the AES_ENCRYPT function to encrypt the social security numbers to maintain confidentiality.

Syntax

AES_ENCRYPT (
value, key
)
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
value
Required
String datatype. Value you want to encrypt.
key
Required
String datatype. Precision of 16 characters or fewer. You can use mapping variables for the key.

Return Value

Encrypted binary value.
NULL if the input is a null value.

Example

The following example returns encrypted values for social security numbers. In this example, the
PowerCenter Integration Service
derives the key from the first three numbers of the social security number using the SUBSTR function:
AES_ENCRYPT( SSN, SUBSTR( SSN,1,3 ))
SSN
ENCRYPTED VALUE
832-17-1672
07FB945926849D2B1641E708C85E4390
832-92-4731
9153ACAB89D65A4B81AD2ABF151B099D
832-46-7552
AF6B5E4E39F974B3F3FB0F22320CC60B
832-53-6194
992D6A5D91E7F59D03B940A4B1CBBCBE
832-81-9528
992D6A5D91E7F59D03B940A4B1CBBCBE

Tip

If the target does not support binary data, use AES_ENCRYPT with the ENC_BASE64 function to store the data in a format compatible with the database.