DECRYPT_BINARY, DECRYPT_BIT, DECRYPT_CHAR, DECRYPT_DB

Return the value resulting from decrypting encrypted data. The decryption functions can decrypt only those functions that were encrypted by using the ENCRYPT function.

The results of the functions are determined by the function specified and the data type of the encrypted data as shown here:

Function Data Type Result
DECRYPT_BINARY BLOB BLOB
DECRYPT_BINARY BINARY or VARBINARY VARBINARY
DECRYPT_BIT CHAR or VARCHAR FOR BIT DATA VARCHAR FOR BIT DATA
DECRYPT_CHAR CHAR, VARCHAR FOR BIT DATA, BINARY, or VARBINARY VARCHAR
DECRYPT_CHAR BLOB CLOB
DECRYPT_DB CHAR, VARCHAR FOR BIT DATA, BINARY, or VARBINARY VARGRAPHIC
DECRYPT_DB BLOB DBCLOB

The password used for decryption is a CHAR or VARCHAR value representing the password that was used to encrypt the data string, if provided. If the password is not provided, the data will be encrypted and decrypted using the ENCRYPTION PASSWORD value assigned using the SET ENCRYPTION PASSWORD command.

Example:

Use the ENCRYPTION PASSWORD value to hold the encryption password:

  SET ENCRYPTION PASSWORD='BEN123';
	   INSERT INTO EMP (SSN) VALUE ENCRYPT('289-46-8832');
	   SELECT DECRYPT_CHAR(SSN) FROM SSN;

The value returned is '289-46-8832'.