Variable-length binary functions - AnalyticDB for MySQL - Alibaba Cloud Documentation Center

AnalyticDB for MySQL supports the following variable-length binary functions:

AES_DECRYPT: uses the 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 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 for a specified 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 SHA-1 checksum of a string.
SHA2: calculates the SHA-2 checksum of a string.
SUBSTR: returns a specified 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 each pair of characters in an argument to a hexadecimal value, converts the hexadecimal value to bytes represented by numbers, and then returns the value 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

Some 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 clusters of V3.1.10.0 or later support AES_DECRYPT_MY.
For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.

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 clusters of V3.1.10.0 or later support AES_ENCRYPT_MY.
For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.

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 clusters of V3.1.9.3 or later support GUNZIP.

For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.

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 clusters of V3.1.9.3 or later support GZIP.

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, left-padded with the Z string to a length of Y characters.
If X is longer than Y, 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 for 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, 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 length of bits required to represent 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 the specified 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 each pair of characters in the X argument as a hexadecimal value, converts the hexadecimal value to bytes represented by numbers, and then returns the value 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 clusters of V3.1.9.3 or later support UNZIP.

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 clusters of V3.1.9.3 or later support ZIP.

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