Use variable-length binary functions - AnalyticDB - Alibaba Cloud Documentation Center

You can use variable-length binary functions to process data of binary data types, such as BINARY, VARBINARY, and BLOB. For example, you can encrypt and decrypt data, extract substrings, and calculate string lengths. This topic describes the syntax of variable-length binary functions and provides examples on how to use the variable-length binary functions in AnalyticDB for MySQL.

AES_DECRYPT: uses the Advanced Encryption Standard (AES) algorithm to decrypt data.
AES_DECRYPT_MY: uses the AES algorithm to decrypt data.
AES_ENCRYPT: uses the AES algorithm to encrypt data.
AES_ENCRYPT_MY: uses the AES algorithm to encrypt data.
CHAR_LENGTH: returns the length of a string, measured in characters.
COMPRESS: compresses a string and returns the result as a binary string.
CRC32: returns the cyclic redundancy check (CRC) code of an argument.
ENCRYPT: encrypts a string.
FROM_BASE64: decodes a Base64-encoded string and returns the result.
GUNZIP: decompresses a binary string based on the GZIP format and returns the binary string result.
GZIP: compresses a binary string based on the GZIP format and returns the result as a binary string.
HEX: converts a string or number to a hexadecimal string and returns the result.
LEFT: returns the leftmost y characters of a string.
LENGTH: returns the length of an argument, measured in bytes.
LOWER: returns an argument in lowercase.
LPAD: returns a string that is left-padded with another string.
LTRIM: removes the leading spaces of a string.
MD5: calculates the message-digest algorithm 5 (MD5) hash value of an argument.
ORD: returns the code of the leftmost character of a string if the character is a multibyte character.
REPEAT: returns a string that repeats a specific number of times.
REVERSE: reverses characters in a string.
RIGHT: returns the rightmost y characters of a string.
RPAD: returns a string that is right-padded with another string.
RTRIM: removes the trailing spaces of a string.
SHA1: calculates the Secure Hash Algorithm 1 (SHA-1) checksum of a string.
SHA2: calculates the Secure Hash Algorithm 2 (SHA-2) checksum of a string.
SUBSTR: returns a specific substring.
TO_BASE64: returns a Base64-encoded string.
TRIM: removes the leading and trailing spaces of a string.
UNCOMPRESS: decompresses a string that is compressed by the COMPRESS() function.
UNCOMPRESSED_LENGTH: returns the length of a string before compression.
UNHEX: interprets pairs of characters in an argument to hexadecimal values, converts the hexadecimal values to bytes represented by numbers, and then returns the result as a binary string.
UNZIP: decompresses a binary string based on the ZIP format and returns the binary string result.
UPPER: returns an argument in uppercase.
ZIP: compresses a binary string based on the ZIP format and returns the result as a binary string.

Usage notes

Specific functions may have dissimilar return values because different clients generate different parsing results. For example, "China" is parsed to "0x4368696E61" by a client. In all examples of this topic, the return values that are generated from Data Management (DMS) are used.

AES_DECRYPT

AES_DECRYPT(VARBINARY X, VARCHAR Y)

Description: This function uses the AES algorithm to decrypt X with Y as the key.
Data type of the return value: VARBINARY.

Example:

SELECT HEX(AES_DECRYPT(AES_ENCRYPT(CAST('China' AS VARBINARY), '0123'),'0123'));

The following information is returned:

+-----------------------------------------------------------------------------+
| HEX(AES_DECRYPT(AES_ENCRYPT(CAST('China' AS VARBINARY), '0123'),'0123'))    |
+-----------------------------------------------------------------------------+
|   4368696E61                                                                |
+-----------------------------------------------------------------------------+

AES_DECRYPT_MY

Important

The AES_DECRYPT_MY function is compatible with MySQL. In AnalyticDB for MySQL, you can decrypt data that is encrypted in MySQL.
Only AnalyticDB for MySQL clusters of V3.1.10.0 or later support the AES_DECRYPT_MY function.
To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.
For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.

AES_DECRYPT_MY(VARBINARY X, VARCHAR Y)

Description: This function uses the AES algorithm to decrypt X with Y as the key.
Data type of the return value: VARBINARY.

Example:

SELECT CAST(AES_DECRYPT_MY(UNHEX('CF3AB34E18CD73CE37D46AD2588BA258'),'key_string') AS CHAR);

The following information is returned:

+---------------------------------------------------------------------------------------+
| CAST(AES_DECRYPT_MY(UNHEX('CF3AB34E18CD73CE37D46AD2588BA258'),'key_string') AS CHAR)  |
+---------------------------------------------------------------------------------------+
| China                                                                                 |
+---------------------------------------------------------------------------------------+

AES_ENCRYPT

AES_ENCRYPT(VARBINARY X, VARCHAR Y)

Description: This function uses the AES algorithm to encrypt X with Y as the key.
Data type of the return value: VARBINARY.

Example:

SELECT HEX(AES_ENCRYPT(CAST('China' AS VARBINARY), '0123'));

The following information is returned:

+-------------------------------------------------------+
|HEX(AES_ENCRYPT(CAST('China' AS VARBINARY), '0123'))   |
+-------------------------------------------------------+
| 6C5CB5E59E3EA58E58CFBE3C76BFBECA                      |
+-------------------------------------------------------+

AES_ENCRYPT_MY

Important

The AES_ENCRYPT_MY function is compatible with MySQL. In MySQL, you can decrypt data that is encrypted in AnalyticDB for MySQL.
Only AnalyticDB for MySQL clusters of V3.1.10.0 or later support the AES_ENCRYPT_MY function.
To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.
For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.

AES_ENCRYPT_MY(VARBINARY X, VARCHAR Y)

Description: This function uses the AES algorithm to encrypt X with Y as the key.
Data type of the return value: VARBINARY.

Example:

SELECT HEX(AES_ENCRYPT_MY('China', 'key_string'));

The following information is returned:

+-------------------------------------------------------+
|HEX(AES_ENCRYPT_MY('China', 'key_string'))             |
+-------------------------------------------------------+
|CF3AB34E18CD73CE37D46AD2588BA258                       | 
+-------------------------------------------------------+

CHAR_LENGTH

CHAR_LENGTH(VARBINARY X)

Description: This function returns the length of the X string, measured in characters.
Data type of the return value: LONG.

Example:

SELECT CHAR_LENGTH(CAST('ABC' AS VARBINARY));

The following information is returned:

+---------------------------------------+
| CHAR_LENGTH(CAST('ABC' AS VARBINARY)) |
+---------------------------------------+
|3                                      | 
+---------------------------------------+

COMPRESS

COMPRESS(VARBINARY X)

Description: This function compresses the X string and returns the result as a binary string.
Data type of the return value: VARBINARY.

Example:

SELECT HEX(COMPRESS(CAST('China' AS VARBINARY)));

The following information is returned:

+--------------------------------------------+
| HEX(COMPRESS(CAST('China' AS VARBINARY)))  |
+--------------------------------------------+
|  05000000789C73CEC8CC4B0400056C01E4        |
+--------------------------------------------+

CRC32

CRC32(VARBINARY X)

Description: This function returns the CRC code of X.
Data type of the return value: LONG.

Example:

SELECT CRC32(CAST('China' AS VARBINARY));

The following information is returned:

+------------------------------------+
| CRC32(CAST('China' AS VARBINARY))  |
+------------------------------------+
| 2704207136                         | 
+------------------------------------+

ENCRYPT

ENCRYPT(VARBINARY X, VARCHAR Y)

Description: This function encrypts the X argument with Y as the salt value.
Data type of the return value: BLOB.

Example:

SELECT ENCRYPT('ABDABC123','KEY');

The following information is returned:

+-----------------------------+
| ENCRYPT('ABDABC123','KEY')  |
+-----------------------------+
| kezazmcIo.aCw               |
+-----------------------------+

FROM_BASE64

FROM_BASE64(VARBINARY X)

Description: This function decodes the Base64-encoded X string and returns the result.
Data type of the return value: BLOB.

Example:

SELECT FROM_BASE64(TO_BASE64(CAST('abc' AS VARBINARY)));

The following information is returned:

+--------------------------------------------------+
| FROM_BASE64(TO_BASE64(CAST('abc' AS VARBINARY))) |
+--------------------------------------------------+
| abc                                              |
+--------------------------------------------------+

GUNZIP

Important

Only AnalyticDB for MySQL clusters of V3.1.9.3 or later support the GUNZIP function.

To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.

For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.

GUNZIP(VARBINARY X)

Description: This function decompresses the X binary string based on the GZIP format and returns the binary string result.
Data type of the return value: VARBINARY.

Example:

SELECT GUNZIP(GZIP(CAST('China' AS VARBINARY)));

The following information is returned:

+--------------------------------------------+
| GUNZIP(GZIP(CAST('China' AS VARBINARY)))   |
+--------------------------------------------+
| China                                      |  
+--------------------------------------------+

GZIP

Important

Only AnalyticDB for MySQL clusters of V3.1.9.3 or later support the GZIP function.

For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.

GZIP(VARBINARY X)

Description: This function compresses the X binary string based on the GZIP format and returns the result as a binary string.
Data type of the return value: VARBINARY.

Example:

SELECT HEX(GZIP(CAST('China' AS VARBINARY)));

The following information is returned:

+------------------------------------------------------+
| HEX(GZIP(CAST('China' AS VARBINARY)))                |
+------------------------------------------------------+
|1F8B080000000000000073CEC8CC4B040020ED2EA105000000    | 
+------------------------------------------------------+

HEX

HEX(VARBINARY X)

Description: This function converts the X string or number to a hexadecimal string.
Data type of the return value: VARCHAR.

Example:

SELECT HEX(CAST('China' AS VARBINARY));

The following information is returned:

+----------------------------------+
| HEX(CAST('China' AS VARBINARY))  |
+----------------------------------+
|  4368696E61                      | 
+----------------------------------+

LEFT

LEFT(VARBINARY X, BIGINT Y)
LEFT(VARBINARY X, DOUBLE Y)

Description: This function returns the leftmost Y characters of the X string.
Data type of the return value: BLOB.

Example:

SELECT LEFT(CAST('China' AS VARBINARY),1000);

The following information is returned:

+-----------------------------------------+
| LEFT(CAST('China' AS VARBINARY), 1000)  |
+-----------------------------------------+
| China                                   | 
+-----------------------------------------+

LENGTH

LENGTH(VARBINARY X)

Description: This function returns the length of the X argument, measured in bytes.
Data type of the return value: LONG.

Example:

SELECT LENGTH(CAST('ABC' AS VARBINARY));

The following information is returned:

 +----------------------------------+
 | LENGTH(CAST('ABC' AS VARBINARY)) |
 +----------------------------------+
 | 3                                |   
 +----------------------------------+

LOWER

LOWER(VARBINARY X)

Description: This function returns the X argument in lowercase.
Data type of the return value: VARBINARY.

Example:

SELECT LOWER(CAST('ABC' AS VARBINARY));

The following information is returned:

+---------------------------------+
| LOWER(CAST('ABC' AS VARBINARY)) |
+---------------------------------+
| abc                             | 
+---------------------------------+

LPAD

LPAD(VARBINARY X, BIGINT Y, VARCHAR Z)
LPAD(VARBINARY X, DOUBLE Y, VARCHAR Z)

Description: This function returns the X string that is left-padded with the Z string to a length of Y characters.
If X is longer than Y characters, the return value is shortened to Y characters.
Data type of the return value: VARBINARY.

Example:

SELECT HEX(LPAD(CAST('China' AS VARBINARY), 7,'-'));

The following information is returned:

+------------------------------------------------+
| HEX(LPAD(CAST('China' AS VARBINARY), 7, '-'))  |
+------------------------------------------------+
| 2D2D4368696E61                                 | 
+------------------------------------------------+

LTRIM

LTRIM(VARBINARY X)

Description: This function removes the leading spaces of the X string.
Data type of the return value: BLOB.

Example:

SELECT LTRIM(CAST('  China  ' AS VARBINARY));

The following information is returned:

+----------------------------------------+
| LTRIM(CAST('  China  ' AS VARBINARY))  |
+----------------------------------------+
| China                                  |
+----------------------------------------+

MD5

MD5(VARBINARY X)

Description: This function returns the MD5 hash value of the X argument.
Data type of the return value: VARCHAR.

Example:

SELECT MD5(CAST('China' AS VARBINARY));

The following information is returned:

+----------------------------------+
| MD5(CAST('China' AS VARBINARY))  |
+----------------------------------+
| ae54a5c026f31ada088992587d92cb3a | 
+----------------------------------+

ORD

ORD(VARBINARY X)

Description: This function returns the code of the leftmost character of the X string if the character is a multibyte character.
Data type of the return value: LONG.

Example:

SELECT ORD(CAST('China' AS VARBINARY));

The following information is returned:

+----------------------------------+
| ORD(CAST('China' AS VARBINARY))  |
+----------------------------------+
|67                                | 
+----------------------------------+

REPEAT

REPEAT(VARBINARY X, DOUBLE Y)
REPEAT(VARBINARY X, BIGINT Y)

Description: This function returns the X string that repeats Y times.
Data type of the return value: VARBINARY.

Example:

SELECT HEX(REPEAT(CAST('China' AS VARBINARY),1));

The following information is returned:

+---------------------------------------------+
| HEX(REPEAT(CAST('China' AS VARBINARY), 1))  |
+---------------------------------------------+
|  4368696E61                                 | 
+---------------------------------------------+

REVERSE

REVERSE(VARBINARY X)

Description: This function reverses characters in the X string.
Data type of the return value: VARBINARY.

Example:

SELECT HEX(REVERSE(CAST('China' AS VARBINARY)));

The following information is returned:

+-------------------------------------------+
| HEX(REVERSE(CAST('China' AS VARBINARY)))  |
+-------------------------------------------+
|  616E696843                               |
+-------------------------------------------+

RIGHT

RIGHT(VARBINARY X, BIGINT Y)
RIGHT(VARBINARY X, DOUBLE Y)

Description: This function returns the rightmost Y characters of the X string.
Data type of the return value: VARBINARY.

Example:

SELECT HEX(RIGHT(CAST('China' AS VARBINARY),1));

The following information is returned:

+--------------------------------------------+
| HEX(RIGHT(CAST('China' AS VARBINARY), 1))  |
+--------------------------------------------+
| 61                                         |
+--------------------------------------------+

RPAD

RPAD(VARBINARY X, BIGINT Y, VARCHAR Z)
RPAD(VARBINARY X, DOUBLE Y, VARCHAR Z)

Description: This function returns the X string, right-padded with the Z string to a length of Y characters.
If X is longer than Y characters, the return value is shortened to Y characters.
Data type of the return value: VARBINARY.

Example:

SELECT HEX(RPAD(CAST('China' AS VARBINARY), 4.7,'x'));

The following information is returned:

+--------------------------------------------------+
| HEX(RPAD(CAST('China' AS VARBINARY), 4.7, 'x'))  |
+--------------------------------------------------+
| 4368696E61                                       | 
+--------------------------------------------------+

RTRIM

RTRIM(VARBINARY X)

Description: This function removes the trailing spaces of the X string.
Data type of the return value: BLOB.

Example:

SELECT RTRIM(CAST('  China  ' AS VARBINARY));

The following information is returned:

+----------------------------------------+
| RTRIM(CAST('  China  ' AS VARBINARY))  |
+----------------------------------------+
|   China                                | 
+----------------------------------------+

SHA1

SHA1(VARBINARY X)

Description: This function calculates the SHA-1 checksum of the X string.
Data type of the return value: VARCHAR.

Example:

SELECT SHA1(CAST('China' AS VARBINARY));

The following information is returned:

+------------------------------------------+
| SHA1(CAST('China' AS VARBINARY))         |
+------------------------------------------+
| d2eaf2aa1512d6596e0a5bae633537c6b8e779a3 |      
+------------------------------------------+

SHA2

SHA2(VARBINARY X, INTEGER Y)
SHA2(VARBINARY X, VARCHAR Y)

Description: This function returns the SHA-2 checksum of the X string. You can choose to use SHA-224, SHA-256, SHA-384, or SHA-512. X is the plaintext string to be hashed. Y is the required bit length of the result, which must be 224, 256, 384, 512, or 0.
Data type of the return value: VARCHAR.

Example:

SELECT SHA2(CAST('China' AS VARBINARY),256);

The following information is returned:

+------------------------------------------------------------------+
| SHA2(CAST('China' AS VARBINARY),256)                            |
+------------------------------------------------------------------+
| 10436829032f361a3de50048de41755140e581467bc1895e6c1a17f423e42d10 |
+------------------------------------------------------------------+

SUBSTR

SUBSTR(VARBINARY X, BIGINT Y, DOUBLE Z)
SUBSTR(VARBINARY X, DOUBLE Y, DOUBLE Z)
SUBSTR(VARBINARY X, BIGINT Y, BIGINT Z)
SUBSTR(VARBINARY X, DOUBLE Y, BIGINT Z)
SUBSTR(VARBINARY X, DOUBLE Y)
SUBSTR(VARBINARY X, BIGINT Y)

Description: This function returns a specific substring.
Data type of the return value: VARBINARY.
Example:

TO_BASE64

TO_BASE64(VARBINARY X)

Description: This function returns the X string that is encoded in the Base64 format.
Data type of the return value: VARCHAR.

Example:

SELECT TO_BASE64(CAST('China' AS VARBINARY));

The following information is returned:

+----------------------------------------+
| TO_BASE64(CAST('China' AS VARBINARY))  |
+----------------------------------------+
| Q2hpbmE=                               |
+----------------------------------------+

TRIM

TRIM(VARBINARY X)

Description: This function removes the leading and trailing spaces of the X string.
Data type of the return value: BLOB.

Example:

SELECT TRIM(CAST('  China  ' AS VARBINARY));

The following information is returned:

+---------------------------------------+
| TRIM(CAST('  China  ' AS VARBINARY))  |
+---------------------------------------+
| China                                 | 
+---------------------------------------+

UNCOMPRESS

UNCOMPRESS(VARBINARY X)

Description: This function decompresses the X string that is compressed by the COMPRESS() function.
Data type of the return value: BLOB.

Example:

SELECT UNCOMPRESS(COMPRESS(CAST('China' AS VARBINARY)));

The following information is returned:

+---------------------------------------------------+
| UNCOMPRESS(COMPRESS(CAST('China' AS VARBINARY)))  |
+---------------------------------------------------+
| China                                             | 
+---------------------------------------------------+

UNCOMPRESSED_LENGTH

UNCOMPRESSED_LENGTH(VARBINARY X)

Description: This function returns the length of the X string before compression.
Data type of the return value: LONG.

Example:

SELECT UNCOMPRESSED_LENGTH(COMPRESS(CAST('China' AS VARBINARY)));

The following information is returned:

+------------------------------------------------------------+
| UNCOMPRESSED_LENGTH(COMPRESS(CAST('China' AS VARBINARY)))  |
+------------------------------------------------------------+
| 5                                                          |
+------------------------------------------------------------+

UNHEX

UNHEX(VARBINARY X)

Description: This function interprets pairs of characters in the X argument as hexadecimal values, converts the hexadecimal values to bytes represented by numbers, and then returns the result as a binary string.
Data type of the return value: VARBINARY.

Example:

SELECT UNHEX(CAST('China' AS VARBINARY));

The following information is returned:

+------------------------------------+
| UNHEX(CAST('China' AS VARBINARY))  |
+------------------------------------+
| NULL                               |  
+------------------------------------+

UNZIP

Important

Only AnalyticDB for MySQL clusters of V3.1.9.3 or later support the UNZIP function.

For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.

UNZIP(VARBINARY X)

Description: This function decompresses the X binary string based on the ZIP format and returns the binary string result.
Data type of the return value: VARBINARY.

Example:

SELECT UNZIP(ZIP(CAST('China' AS VARBINARY)));

The following information is returned:

+---------------------------------------+
| UNZIP(ZIP(CAST('China' AS VARBINARY)))|
+---------------------------------------+
| China                                 | 
+---------------------------------------+

UPPER

UPPER(VARBINARY X)

Description: This function returns the X argument in uppercase.
Data type of the return value: VARBINARY.

Example:

SELECT UPPER(CAST('abc' AS VARBINARY));

The following information is returned:

+---------------------------------+
| UPPER(CAST('abc' AS VARBINARY)) |
+---------------------------------+
| ABC                             | 
+---------------------------------+

ZIP

Important

Only AnalyticDB for MySQL clusters of V3.1.9.3 or later support the ZIP function.

For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.

ZIP(VARBINARY X)

Description: This function compresses the X binary string based on the ZIP format and returns the result as a binary string.
Data type of the return value: VARBINARY.

Example:

SELECT UNZIP(ZIP(CAST('China' AS VARBINARY)));

The following information is returned:

+---------------------------------------+
| UNZIP(ZIP(CAST('China' AS VARBINARY)))|
+---------------------------------------+
| China                                 | 
+---------------------------------------+