All Products
Search

This Product

    AnalyticDB:Variable-length binary functions

    Document Center

    AnalyticDB:Variable-length binary functions

    Last Updated:Mar 28, 2026

    Variable-length binary functions process data of binary types such as BINARY, VARBINARY, and BLOB. Use them to encrypt and decrypt data, compress and decompress strings, encode and decode values, compute hashes, and manipulate binary strings.

    All examples in this topic use return values generated from Data Management (DMS). Return values may differ in other clients because of client-side parsing differences. For example, 'China' is parsed as 0x4368696E61 by some clients.

    Function overview

    Encryption and decryption

    FunctionDescriptionReturn type
    AES_DECRYPT(X, Y)Decrypts X using the Advanced Encryption Standard (AES) algorithm with Y as the keyVARBINARY
    AES_DECRYPT_MY(X, Y)Decrypts X using AES with Y as the key. MySQL-compatible — decrypts data encrypted in MySQL. Requires V3.1.10.0 or laterVARBINARY
    AES_ENCRYPT(X, Y)Encrypts X using AES with Y as the keyVARBINARY
    AES_ENCRYPT_MY(X, Y)Encrypts X using AES with Y as the key. MySQL-compatible — data encrypted with this function can be decrypted in MySQL. Requires V3.1.10.0 or laterVARBINARY
    ENCRYPT(X, Y)Encrypts X with Y as the salt valueBLOB

    Compression and decompression

    FunctionDescriptionReturn typeVersion
    COMPRESS(X)Compresses X and returns the result as a binary stringVARBINARY—
    UNCOMPRESS(X)Decompresses X, which must have been compressed by COMPRESS()BLOB—
    UNCOMPRESSED_LENGTH(X)Returns the length of X before compressionLONG—
    GZIP(X)Compresses X using GZIP format and returns the result as a binary stringVARBINARYV3.1.9.3+
    GUNZIP(X)Decompresses X using GZIP format and returns the binary string resultVARBINARYV3.1.9.3+
    ZIP(X)Compresses X using ZIP format and returns the result as a binary stringVARBINARYV3.1.9.3+
    UNZIP(X)Decompresses X using ZIP format and returns the binary string resultVARBINARYV3.1.9.3+

    Encoding and decoding

    FunctionDescriptionReturn type
    HEX(X)Converts X to a hexadecimal stringVARCHAR
    UNHEX(X)Interprets pairs of characters in X as hexadecimal values, converts them to bytes, and returns the result as a binary stringVARBINARY
    FROM_BASE64(X)Decodes the Base64-encoded string X and returns the resultBLOB
    TO_BASE64(X)Encodes X in Base64 format and returns the resultVARCHAR

    Hashing and checksums

    FunctionDescriptionReturn type
    MD5(X)Returns the message-digest algorithm 5 (MD5) hash of XVARCHAR
    SHA1(X)Returns the Secure Hash Algorithm 1 (SHA-1) checksum of XVARCHAR
    SHA2(X, Y)Returns the SHA-2 checksum of X. Y specifies the bit length: 224, 256, 384, 512, or 0VARCHAR
    CRC32(X)Returns the cyclic redundancy check (CRC) code of XLONG

    String manipulation

    FunctionDescriptionReturn type
    CHAR_LENGTH(X)Returns the length of X measured in charactersLONG
    LENGTH(X)Returns the length of X measured in bytesLONG
    LEFT(X, Y)Returns the leftmost Y characters of XBLOB
    RIGHT(X, Y)Returns the rightmost Y characters of XVARBINARY
    SUBSTR(X, Y[, Z])Returns a substring starting at position Y. Z specifies the lengthVARBINARY
    LPAD(X, Y, Z)Left-pads X with Z to a total length of Y characters. Truncates X to Y characters if X is longer than YVARBINARY
    RPAD(X, Y, Z)Right-pads X with Z to a total length of Y characters. Truncates X to Y characters if X is longer than YVARBINARY
    LTRIM(X)Removes leading spaces from XBLOB
    RTRIM(X)Removes trailing spaces from XBLOB
    TRIM(X)Removes leading and trailing spaces from XBLOB
    LOWER(X)Returns X in lowercaseVARBINARY
    UPPER(X)Returns X in uppercaseVARBINARY
    REPEAT(X, Y)Returns X repeated Y timesVARBINARY
    REVERSE(X)Returns X with characters in reversed orderVARBINARY
    ORD(X)Returns the code of the leftmost character of X when that character is a multibyte characterLONG

    Usage notes

    • Client-specific output: Return values may differ between clients due to different parsing behaviors. The examples in this topic use output from DMS.

    • Version requirements: GUNZIP, GZIP, UNZIP, and ZIP require V3.1.9.3 or later. AES_DECRYPT_MY and AES_ENCRYPT_MY require V3.1.10.0 or later.

      • To check the minor version of a Data Lakehouse Edition cluster, run SELECT adb_version();. To update, contact technical support.

      • To view or update the minor version of a Data Warehouse Edition cluster, see Update the minor version of a cluster.

    • AES_ENCRYPT vs AES_ENCRYPT_MY: Use AES_ENCRYPT when both encryption and decryption occur within AnalyticDB for MySQL. Use AES_ENCRYPT_MY when the encrypted data must be decryptable in MySQL, or when decrypting data that was encrypted in MySQL.

    • NULL behavior: UNHEX() returns NULL when the input contains characters outside the hexadecimal range (0–9, A–F).

    • Binary result storage: Functions that return VARBINARY or BLOB may produce arbitrary byte values. To display binary results as readable text, wrap the output in HEX().

    AES_DECRYPT

    AES_DECRYPT(VARBINARY X, VARCHAR Y)

    Decrypts X using AES with Y as the key.

    Example:

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

    The following result is returned:

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

    The following result is returned:

    +---------------------------------------+
| UNZIP(ZIP(CAST('China' AS VARBINARY)))|
+---------------------------------------+
| China                                 | 
+---------------------------------------+
    SELECT HEX(GZIP(CAST('China' AS VARBINARY)));

    The following result is returned:

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

    The following result is returned:

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

    The following result is returned:

    +------------------------------------+
| UNHEX(CAST('China' AS VARBINARY))  |
+------------------------------------+
| NULL                               |  
+------------------------------------+
    SELECT UNCOMPRESSED_LENGTH(COMPRESS(CAST('China' AS VARBINARY)));

    The following result is returned:

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

    The following result is returned:

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

    The following result is returned:

    +---------------------------------------+
| TRIM(CAST('  China  ' AS VARBINARY))  |
+---------------------------------------+
| China                                 | 
+---------------------------------------+
    SELECT TO_BASE64(CAST('China' AS VARBINARY));

    The following result is returned:

    +----------------------------------------+
| TO_BASE64(CAST('China' AS VARBINARY))  |
+----------------------------------------+
| Q2hpbmE=                               |
+----------------------------------------+
    SELECT SHA2(CAST('China' AS VARBINARY),256);

    The following is returned:

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

    The following result is returned:

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

    The following result is returned:

    +----------------------------------------+
| RTRIM(CAST('  China  ' AS VARBINARY))  |
+----------------------------------------+
|   China                                | 
+----------------------------------------+
    SELECT HEX(RPAD(CAST('China' AS VARBINARY), 4.7,'x'));

    The following result is returned:

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

    The following result is returned:

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

    The following result is returned:

    +-------------------------------------------+
| HEX(REVERSE(CAST('China' AS VARBINARY)))  |
+-------------------------------------------+
|  616E696843                               |
+-------------------------------------------+
    SELECT HEX(REPEAT(CAST('China' AS VARBINARY),1));

    The following result is returned:

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

    The following result is returned:

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

    The following result is returned:

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

    The following result is returned:

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

    The following result is returned:

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

    The following result is returned:

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

    The following result is returned:

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

    The following result is returned:

    +------------------------------------+
| CRC32(CAST('China' AS VARBINARY))  |
+------------------------------------+
| 2704207136                         | 
+------------------------------------+
    SELECT HEX(COMPRESS(CAST('China' AS VARBINARY)));

    The following result is returned:

    +--------------------------------------------+
| HEX(COMPRESS(CAST('China' AS VARBINARY)))  |
+--------------------------------------------+
|  05000000789C73CEC8CC4B0400056C01E4        |
+--------------------------------------------+
    SELECT HEX(AES_ENCRYPT_MY('China', 'key_string'));

    The following result is returned:

    +-------------------------------------------------------+
|HEX(AES_ENCRYPT_MY('China', 'key_string'))             |
+-------------------------------------------------------+
|CF3AB34E18CD73CE37D46AD2588BA258                       | 
+-------------------------------------------------------+
    SELECT HEX(AES_ENCRYPT(CAST('China' AS VARBINARY), '0123'));

    The following result is returned:

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

    The following result is returned:

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

    Output:

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

    AES_DECRYPT_MY

    Important

    AES_DECRYPT_MY is compatible with MySQL. Use it to decrypt data that was encrypted in MySQL. Requires AnalyticDB for MySQL V3.1.10.0 or later.

    AES_DECRYPT_MY(VARBINARY X, VARCHAR Y)

    Decrypts X using AES with Y as the key.

    Example:

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

    Output:

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

    AES_ENCRYPT

    AES_ENCRYPT(VARBINARY X, VARCHAR Y)

    Encrypts X using AES with Y as the key.

    Example:

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

    Output:

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

    AES_ENCRYPT_MY

    Important

    AES_ENCRYPT_MY is compatible with MySQL. Data encrypted with this function can be decrypted in MySQL. Requires AnalyticDB for MySQL V3.1.10.0 or later.

    AES_ENCRYPT_MY(VARBINARY X, VARCHAR Y)

    Encrypts X using AES with Y as the key.

    Example:

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

    Output:

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

    CHAR_LENGTH

    CHAR_LENGTH(VARBINARY X)

    Returns the length of X measured in characters.

    Example:

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

    Output:

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

    COMPRESS

    COMPRESS(VARBINARY X)

    Compresses X and returns the result as a binary string. Use HEX() to display the result as readable text.

    Example:

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

    Output:

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

    CRC32

    CRC32(VARBINARY X)

    Returns the cyclic redundancy check (CRC) code of X.

    Example:

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

    Output:

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

    ENCRYPT

    ENCRYPT(VARBINARY X, VARCHAR Y)

    Encrypts X with Y as the salt value.

    Example:

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

    Output:

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

    FROM_BASE64

    FROM_BASE64(VARBINARY X)

    Decodes the Base64-encoded string X and returns the result.

    Example:

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

    Output:

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

    GUNZIP

    Important

    Requires AnalyticDB for MySQL V3.1.9.3 or later.

    GUNZIP(VARBINARY X)

    Decompresses X using GZIP format and returns the binary string result.

    Example:

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

    Output:

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

    GZIP

    Important

    Requires AnalyticDB for MySQL V3.1.9.3 or later.

    GZIP(VARBINARY X)

    Compresses X using GZIP format and returns the result as a binary string. Use HEX() to display the result as readable text.

    Example:

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

    Output:

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

    HEX

    HEX(VARBINARY X)

    Converts X to a hexadecimal string.

    Example:

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

    Output:

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

    LEFT

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

    Returns the leftmost Y characters of X.

    Example:

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

    Output:

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

    LENGTH

    LENGTH(VARBINARY X)

    Returns the length of X measured in bytes.

    Example:

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

    Output:

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

    LOWER

    LOWER(VARBINARY X)

    Returns X in lowercase.

    Example:

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

    Output:

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

    LPAD

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

    Left-pads X with Z to a total length of Y characters. If X is longer than Y characters, the result is truncated to Y characters.

    Example:

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

    Output:

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

    LTRIM

    LTRIM(VARBINARY X)

    Removes leading spaces from X.

    Example:

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

    Output:

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

    MD5

    MD5(VARBINARY X)

    Returns the MD5 hash of X.

    Example:

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

    Output:

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

    ORD

    ORD(VARBINARY X)

    Returns the code of the leftmost character of X when that character is a multibyte character.

    Example:

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

    Output:

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

    REPEAT

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

    Returns X repeated Y times.

    Example:

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

    Output:

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

    REVERSE

    REVERSE(VARBINARY X)

    Returns X with characters in reversed order.

    Example:

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

    Output:

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

    RIGHT

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

    Returns the rightmost Y characters of X.

    Example:

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

    Output:

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

    RPAD

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

    Right-pads X with Z to a total length of Y characters. If X is longer than Y characters, the result is truncated to Y characters.

    Example:

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

    Output:

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

    RTRIM

    RTRIM(VARBINARY X)

    Removes trailing spaces from X.

    Example:

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

    Output:

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

    SHA1

    SHA1(VARBINARY X)

    Returns the SHA-1 checksum of X.

    Example:

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

    Output:

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

    SHA2

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

    Returns the SHA-2 checksum of X. Y specifies the output bit length and must be one of: 224, 256, 384, 512, or 0. Supported variants: SHA-224, SHA-256, SHA-384, and SHA-512.

    Example:

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

    Output:

    +------------------------------------------------------------------+
| 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)

    Returns a substring of X starting at position Y. Z specifies the number of characters to return.

    TO_BASE64

    TO_BASE64(VARBINARY X)

    Encodes X in Base64 format and returns the result.

    Example:

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

    Output:

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

    TRIM

    TRIM(VARBINARY X)

    Removes leading and trailing spaces from X.

    Example:

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

    Output:

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

    UNCOMPRESS

    UNCOMPRESS(VARBINARY X)

    Decompresses X, which must have been compressed by the COMPRESS() function.

    Example:

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

    Output:

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

    UNCOMPRESSED_LENGTH

    UNCOMPRESSED_LENGTH(VARBINARY X)

    Returns the length of X before compression.

    Example:

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

    Output:

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

    UNHEX

    UNHEX(VARBINARY X)

    Interprets pairs of characters in X as hexadecimal values, converts them to bytes, and returns the result as a binary string. Returns NULL if the input contains characters outside the hexadecimal range (0–9, A–F).

    Example:

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

    Output:

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

    The result is NULL because 'China' contains characters outside the hexadecimal range (0–9, A–F).

    UNZIP

    Important

    Requires AnalyticDB for MySQL V3.1.9.3 or later.

    UNZIP(VARBINARY X)

    Decompresses X using ZIP format and returns the binary string result.

    Example:

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

    Output:

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

    UPPER

    UPPER(VARBINARY X)

    Returns X in uppercase.

    Example:

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

    Output:

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

    ZIP

    Important

    Requires AnalyticDB for MySQL V3.1.9.3 or later.

    ZIP(VARBINARY X)

    Compresses X using ZIP format and returns the result as a binary string. Use HEX() to display the result as readable text.

    Example:

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

    Output:

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