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

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

LENGTH: returns the length of an argument, measured in bytes.
CHAR_LENGTH: returns the length of a string, measured in characters.
ORD: returns the code of the leftmost character of a string if the character is a multibyte character.
HEX: converts a string or number to a hexadecimal string and returns the result.
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.
MD5: calculates the MD5 hash value of an argument.
SHA1: calculates the SHA-1 checksum of a string.
CRC32: returns the cyclic redundancy check (CRC) code of an argument.
LOWER: returns an argument in lowercase.
UPPER: returns an argument in uppercase.

LTRIM: removes the leading spaces of a string.
RTRIM: removes the trailing spaces of a string.
TRIM: removes the leading and trailing spaces of a string.
COMPRESS: compresses a string and returns the result as a binary string.
UNCOMPRESS: decompresses a string compressed by the COMPRESS() function.
UNCOMPRESSED_LENGTH: returns the length of a string before compression.
ENCRYPT: encrypts a string.
AES_ENCRYPT: uses the AES algorithm to encrypt data.
AES_DECRYPT: uses the AES algorithm to decrypt data.

SUBSTR: returns a specified substring.
LEFT: returns the leftmost y characters of a string.
RIGHT: returns the rightmost y characters of a string.
REPEAT: returns a string that repeats for a specified number of times.
REVERSE: reverses characters in a string.
SHA2: calculates the SHA-2 checksum of a string.
LPAD: returns a string that is left-padded with another string.
RPAD: returns a string that is right-padded with another string.
TO_BASE64: returns a Base64-encoded string.
FROM_BASE64: decodes a Base64-encoded string and returns the result.

LENGTH

length(varbinary x)

Description: This function returns the length of the x argument, measured in bytes.
Return value type: LONG.

Example:

    select length(CAST('abc' AS VARBINARY));
    +----------------------------------+
    | length(CAST('abc' AS varbinary)) |
    +----------------------------------+
    |                                3 |

CHAR_LENGTH

char_length(varbinary x)

Description: This function returns the length of the x string, measured in characters.
Return value type: LONG.

Example:

    select char_length(CAST('abc' AS VARBINARY));
    +---------------------------------------+
    | char_length(CAST('abc' AS varbinary)) |
    +---------------------------------------+
    |                                     3 |

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.
Return value type: LONG.

Example:

    select ord(CAST('China' AS VARBINARY));
    +----------------------------------+
    | ord(CAST('China' AS varbinary))   |
    +----------------------------------+
    |                              228 |

HEX

hex(varbinary x)

Description: This function converts the x string or number to a hexadecimal string.
Return value type: VARCHAR.

Example:

    select hex(CAST('China' AS VARBINARY));
    +----------------------------------+
    | hex(CAST('China' AS varbinary))   |
    +----------------------------------+
    | E4B8ADE59BBD                     |

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.
Return value type: VARBINARY.

Example:

    select unhex(CAST('China' AS VARBINARY));
    +------------------------------------+
    | unhex(CAST('China' AS varbinary))   |
    +------------------------------------+
    | NULL                               |

MD5

md5(varbinary x)

Description: This function returns the MD5 hash value of the x argument.
Return value type: VARCHAR.

Example:

    select md5(CAST('China' AS VARBINARY));
    +----------------------------------+
    | md5(CAST('China' AS varbinary))   |
    +----------------------------------+
    | c13dceabcb143acd6c9298265d618a9f |

SHA1

sha1(varbinary x)

Description: This function calculates the SHA-1 checksum of the x string.
Return value type: VARCHAR.

Example:

    select sha1(CAST('China' AS VARBINARY));
    +------------------------------------------+
    | sha1(CAST('China' AS varbinary))          |
    +------------------------------------------+
    | 101806f57c322fb403a9788c4c24b79650d02e77 |

CRC32

crc32(varbinary x)

Description: This function returns the CRC code of x.
Return value type: LONG.

Example:

    select crc32(CAST('China' AS VARBINARY));
    +------------------------------------+
    | crc32(CAST('China' AS varbinary))   |
    +------------------------------------+
    |                          737014929 |

LOWER

lower(varbinary x)

Description: This function returns the x argument in lowercase.
Return value type: VARBINARY.

Example:

    select lower(CAST('ABC' AS VARBINARY));
    +---------------------------------+
    | lower(CAST('ABC' AS varbinary)) |
    +---------------------------------+
    | abc                             |

UPPER

upper(varbinary x)

Description: This function returns the x argument in uppercase.
Return value type: VARBINARY.

Example:

    select upper(CAST('abc' AS VARBINARY));
    +---------------------------------+
    | upper(CAST('abc' AS varbinary)) |
    +---------------------------------+
    | ABC                             |

LTRIM

ltrim(varbinary x)

Description: This function removes the leading spaces of the x string.
Return value type: VARBINARY.

Example:

    select ltrim(CAST('  China  ' AS VARBINARY));
    +----------------------------------------+
    | ltrim(CAST('  China  ' AS varbinary))   |
    +----------------------------------------+
    | China                                   |

RTRIM

rtrim(varbinary x)

Description: This function removes the trailing spaces of the x string.
Return value type: VARBINARY.

Example:

    select rtrim(CAST('  China  ' AS VARBINARY));
    +----------------------------------------+
    | rtrim(CAST('  China  ' AS varbinary))   |
    +----------------------------------------+
    |   China                                 |

TRIM

trim(varbinary x)

Description: This function removes the leading and trailing spaces of the x string.
Return value type: VARBINARY.

Example:

select trim(CAST('  China  ' AS VARBINARY));
    +---------------------------------------+
    | trim(CAST('  China  ' AS varbinary))   |
    +---------------------------------------+
    | China                                  |

COMPRESS

compress(varbinary x)

Description: This function compresses the x string and returns the result as a binary string.
Return value type: VARBINARY.

Example:

    select hex(compress(CAST('China' AS VARBINARY)));
    +--------------------------------------------+
    | hex(compress(CAST('China' AS varbinary)))   |
    +--------------------------------------------+
    | 06000000789C7BB263EDD3D97B01104C0487       |

UNCOMPRESS

uncompress(varbinary x)

Description: This function decompresses the x string compressed by the COMPRESS() function.
Return value type: VARBINARY.

Example:

    select uncompress(compress(CAST('China' AS VARBINARY)));
    +---------------------------------------------------+
    | 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.
Return value type: LONG.

Example:

    select uncompressed_length(compress(CAST('China' AS VARBINARY)));
    +------------------------------------------------------------+
    | uncompressed_length(compress(CAST('China' AS varbinary)))   |
    +------------------------------------------------------------+
    |                                                          6 |

ENCRYPT

encrypt(varbinary x, varchar y)

Description: This function encrypts the x argument with y as the salt value.
Return value type: VARBINARY.

Example:

    select encrypt('abdABC123','key');
    +-----------------------------+
    | encrypt('abdABC123', 'key') |
    +-----------------------------+
    | kezazmcIo.aCw               |

AES_ENCRYPT

aes_encrypt(varbinary x, varchar y)

Description: This function uses the AES algorithm to encrypt x with y as the key.
Return value type: VARBINARY.

Example:

    select HEX(aes_encrypt(CAST('China' AS VARBINARY), '0123'));
    +-------------------------------------------------------+
    | HEX(aes_encrypt(CAST('China' AS VARBINARY), '0123'))   |
    +-------------------------------------------------------+
    | DFB166F0A03113AA848C0CE545D58757                      |

AES_DECRYPT

aes_decrypt(varbinary x, varchar y)

Description: This function uses the AES algorithm to decrypt x with y as the key.
Return value type: VARBINARY.

Example:

    select HEX(aes_decrypt(aes_encrypt(CAST('China' AS VARBINARY), '0123'),'0123'));
    +-----------------------------------------------------------------------------+
    | HEX (aes_decrypt(aes_encrypt(CAST('China' AS VARBINARY), '0123'),'0123'))    |
    +-----------------------------------------------------------------------------+
    | E4B8ADE59BBD                                                                |

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.
Return value type: VARBINARY.

Example:

    select HEX(substr(CAST('China' AS VARBINARY), 2));
    +---------------------------------------------+
    | HEX(substr(CAST('China' AS VARBINARY), 2))   |
    +---------------------------------------------+
    | B8ADE59BBD                                  |
    +---------------------------------------------+

    +----------------------------------------------------+
    | HEX(substr(CAST('China' AS VARBINARY), 2.7, 1.7))   |
    +----------------------------------------------------+
    | ADE5                                               |
    +----------------------------------------------------+

LEFT

left(varbinary x, bigint y)
left(varbinary x, double y)

Description: This function returns the leftmost y characters of the x string.
Return value type: VARBINARY.

Example:

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

RIGHT

right(varbinary x, bigint y)
right(varbinary x, double y)

Description: This function returns the rightmost y characters of the x string.
Return value type: VARBINARY.

Example:

    select HEX(right(CAST('China' AS VARBINARY),1));
    +--------------------------------------------+
    | HEX(RIGHT(CAST('China' AS VARBINARY), 1))   |
    +--------------------------------------------+
    | BD                                         |

REPEAT

repeat(varbinary x, double y)
repeat(varbinary x, bigint y)

Description: This function returns the x string that repeats for y times.
Return value type: VARBINARY.

Example:

    select HEX(repeat(CAST('China' AS VARBINARY),1));
    +---------------------------------------------+
    | HEX(repeat(CAST('China' AS VARBINARY), 1))   |
    +---------------------------------------------+
    | E4B8ADE59BBD                                |

REVERSE

reverse(varbinary x)

Description: This function reverses characters in the x string.
Return value type: VARBINARY.

Example:

    select HEX(reverse(CAST('China' AS VARBINARY)));
    +-------------------------------------------+
    | HEX(reverse(CAST('China' AS VARBINARY)))   |
    +-------------------------------------------+
    | BD9BE5ADB8E4                              |

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.
Return value type: VARCHAR.

Example:

    select sha2(CAST('China' AS VARBINARY),256);
    +------------------------------------------------------------------+
    | sha2(CAST('China' AS varbinary), 256)                             |
    +------------------------------------------------------------------+
    | f0e9521611bb290d7b09b8cd14a63c3fe7cbf9a2f4e0090d8238d22403d35182 |

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.
Return value type: VARBINARY.

Example:

    select HEX(lpad(CAST('China' AS VARBINARY), 7,'-'));
    +------------------------------------------------+
    | HEX(lpad(CAST('China' AS VARBINARY), 7, '-'))   |
    +------------------------------------------------+
    | 2DE4B8ADE59BBD                                 |

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.
Return value type: VARBINARY.

Example:

    select HEX(rpad(CAST('China' AS VARBINARY), 4.7,'x'));
    +--------------------------------------------------+
    | HEX(rpad(CAST('China' AS VARBINARY), 4.7, 'x'))   |
    +--------------------------------------------------+
    | E4B8ADE59B                                       |

TO_BASE64

to_base64(varbinary x)

Description: This function returns the x string encoded in the Base64 format.
Return value type: VARCHAR.

Example:

    select to_base64(CAST('China' AS VARBINARY));
    +----------------------------------------+
    | to_base64(CAST('China' AS varbinary))   |
    +----------------------------------------+
    | 5Lit5Zu9                               |

FROM_BASE64

from_base64(varbinary x)

Description: This function decodes the Base64-encoded x string and returns the result.
Return value type: VARBINARY.

Example:

    select from_base64(to_base64(CAST('abc' AS VARBINARY)));
    +--------------------------------------------------+
    | from_base64(to_base64(CAST('abc' AS varbinary))) |
    +--------------------------------------------------+
    | abc                                              |