Varchar Unicode UDFs

The Varchar Unicode UDFs accept the string data encoded in the UNICODE character set.

Important: Do not exceed the maximum output buffer length when using the result length parameter (resultlen) in the Varchar Unicode UDFs.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

pty_varcharunicodeenc

This UDF protects the Unicode string using an Encryption data element for encryption.

Signature:

pty_varcharunicodeenc(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:
The function returns the protected VARBYTE value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharunicodeenc (TRANSLATE(CAST('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE), 'AES_128',100,0,0 );

pty_varcharunicodedec

This UDF unprotects the protected Unicode string data.

Signature:

pty_varcharunicodedec(col VARBYTE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected Unicode character value.
• The function returns NULL when user has no access to the data in the policy.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharunicodedec(pty_varcharunicodeenc(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE, 'AES256',100,0,0), 'AES256',100,0,0 ));

pty_varcharunicodedecex

This UDF unprotects the protected Unicode string data.

Signature:

pty_varcharunicodedecex(col VARBYTE, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Returns:

• The function returns an unprotected character value.
• The function returns an error instead of NULL if the user does not have access.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

Example:

SELECT pty_varcharunicodedecex(pty_varcharunicodeenc(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE), 'AES256', 100, 0,0), 'AES256', 100, 0,0);

pty_varcharunicodeins

This UDF protects Unicode string data using type-preserving data elements, such as, tokens, Format Preserving Encryption (FPE) data elements, and No Encryption for access control.

Signature:

pty_varcharunicodeins(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

Note: For pty_varcharunicodeins, set the resultlen parameter to four times the input buffer length for optimal results.
If the calculated value (four times the input buffer length) exceeds the maximum configured output buffer length, then it is recommended to use the maximum allowed output buffer length.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

Returns:
The function returns the protected VARCHAR value.

Exception:
If the user does not have protect access rights in the policy, then the UDF terminates with an error message explaining what went wrong.

Example for Unicode Gen2:

The Unicode Gen2 data elements supports the newly introduced SLT_X_1 tokenizer along with the existing SLT_1_3 tokenizer.
For more information about the Unicode Gen2 data elements, refer to Unicode Gen2.

SELECT pty_varcharunicodeins(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USINGLATIN_TO_UNICODE), 'TE_UG2_SLT_13_L2R2_Y_BasicLatin', 100, 0,0);

SELECT pty_varcharunicodeins(TRANSLATE(CAST ('ϠϡϢϣϥϦ' AS VARCHAR(1000)) USINGLATIN_TO_UNICODE), 'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE', 1000, 0,0);

pty_varcharunicodesel

This UDF unprotects Unicode string data protected by data elements, such as, tokens, Format Preserving Encryption (FPE) data elements, and No Encryption for access control.

Warning: This UDF does not support masking.

Signature:

pty_varcharunicodesel(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

For pty_varcharunicodesel, you must set the resultlen parameter to four times the input buffer length for optimal results.
If the calculated value (four times the input buffer length) exceeds the maximum configured output buffer length, then it is recommended to use the maximum allowed output buffer length.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

Returns:

• The function returns an unprotected character value.
• The function returns a protected value if this option is configured in the policy and the user does not have access to data.
• The function returns NULL when the user has no access to data in the policy.

Exception:

• If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

If the input data length exceeds the given output buffer length, then the audit logs are blocked and the following error message appears:

Input or output buffer is too small

Example for Unicode Gen2:

The Unicode Gen2 data elements support the newly introduced SLT_X_1 tokenizer along with the existing SLT_1_3 tokenizer.
For more information about the Unicode Gen2 data elements, refer to Unicode Gen2.

select pty_varcharunicodesel(pty_varcharunicodeins(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE),'TE_UG2_SLT_13_L2R2_Y_BasicLatin', 100, 0,0),'TE_UG2_SLT_13_L2R2_Y_BasicLatin', 100, 0,0);

select pty_varcharunicodesel(pty_varcharunicodeins(TRANSLATE(CAST ('ϠϡϢϣϥϦ' AS VARCHAR(1000)) USINGLATIN_TO_UNICODE), 'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE', 1000, 0,0), 'TE_UG2_SLTX1_L2R2_N_IPA_Greek_Coptic_UTF16LE', 1000, 0,0);

pty_varcharunicodeselex

This UDF unprotects Unicode string data protected by data elements, such as, tokens, Format Preserving Encryption (FPE) data elements, and No Encryption for access control.

Warning: This UDF does not support masking.

Signature:

pty_varcharunicodeselex(col VARCHAR, dataelement VARCHAR, resultlen INTEGER, communicationid INTEGER, scid INTEGER)

Parameters:

For pty_varcharunicodeselex, set the resultlen parameter to four times the input buffer length for optimal results.
If the calculated value (four times the input buffer length) exceeds the maximum configured output buffer length, then it is recommended to use the maximum allowed output buffer length.
For more information about the maximum output buffer length, for each Varchar Unicode UDF, refer to Installing the Teradata Objects.

Returns:

• The function returns an unprotected character value.
• The function returns the protected value if this option is configured in the policy and the user does not have access to the data.
• The function returns an error instead of NULL if the user does not have access.

Exception:
If you configure an exception in the policy and the user does not have access, then the UDF terminates with an error message explaining what went wrong.

If the input data length exceeds the given output buffer length, then the audit logs are blocked and the following error message appears:

Input or output buffer is too small

.

Example:

select pty_varcharunicodeselex(pty_varcharunicodeins(TRANSLATE(CAST ('ProtegrityProt' AS VARCHAR(50)) USING LATIN_TO_UNICODE), 'NoEncryption', 100, 0,0), 'NoEncryption', 100, 0,0);