This topic describes the syntax of binary functions and provides examples.
Simple Log Service supports the following binary functions.
-
In a Simple Log Service query statement, strings must be enclosed in single quotation marks ('). Unquoted literals or literals enclosed in double quotation marks (") represent field or column names. For example, 'status' is the string 'status', while status or "status" refers to the log field status.
-
varbinary is a binary character type, and varchar is a variable-length character type.
|
Function |
Syntax |
Description |
SQL support |
SPL support |
|
from_base64(x) |
Decodes a Base64-encoded string into binary data. |
√ |
√ |
|
|
from_base64url(x) |
Decodes a URL-safe Base64 string into binary data. |
√ |
× |
|
|
from_big_endian_64(x) |
Decodes a bigint from its big-endian binary representation. |
√ |
× |
|
|
from_hex(x) |
Converts a hexadecimal string into binary data. |
√ |
√ |
|
|
length(x) |
Returns the length of varbinary data in bytes or the number of characters in a varchar string. |
√ |
× |
|
|
md5(x) |
Computes the MD5 hash of binary data. |
√ |
√ |
|
|
to_base64(x) |
Encodes binary data into a Base64-encoded string. |
√ |
√ |
|
|
to_base64url(x) |
Encodes binary data into a URL-safe Base64 string. |
√ |
× |
|
|
to_hex(x) |
Converts binary data into a hexadecimal string. |
√ |
√ |
|
|
to_big_endian_64(x) |
Encodes a bigint into its big-endian binary representation. |
√ |
× |
|
|
sha1(x) |
Computes the SHA-1 hash of binary data. |
√ |
√ |
|
|
sha256(x) |
Computes the SHA-256 hash of binary data. |
√ |
√ |
|
|
sha512(x) |
Computes the SHA-512 hash of binary data. |
√ |
√ |
|
|
xxhash64(x) |
Computes the xxHash64 hash of binary data. |
√ |
√ |
from_base64 function
The from_base64 function decodes a Base64-encoded string into binary data.
Syntax
from_base64(x)Parameters
|
Parameter |
Description |
|
x |
A varchar string. This string must be Base64-encoded. |
Return value type
varbinary
The varbinary type can contain invisible characters. The Simple Log Service console displays the result in Base64 format.
-
To view a return value with invisible characters, use the to_hex function to convert it to a hexadecimal string.
-
If a return value contains only visible characters, use the from_utf8 function to convert it to a UTF-8 string.
Examples
Decode a Base64-encoded string into binary data and then convert the binary data to a hexadecimal string.
-
Query statement
* | SELECT to_hex(from_base64('c2xz')) -
Query results: The value of the _col0 column is
736C73.
from_base64url function
The from_base64url function decodes a URL-safe Base64 string into binary data.
Syntax
from_base64url(x)Parameters
|
Parameter |
Description |
|
x |
A varchar string. This string must be a URL-safe, Base64-encoded string. |
Return value type
varbinary
The varbinary type can contain invisible characters. The Simple Log Service console displays the result in Base64 format.
-
To view a return value with invisible characters, use the to_hex function to convert it to a hexadecimal string.
-
If a return value contains only visible characters, use the from_utf8 function to convert it to a UTF-8 string.
Examples
Decode a URL-safe Base64 string into binary data.
-
Query statement
* | SELECT to_hex(from_base64url('c2xz')) -
Query results: The value of the _col0 column is
736C73.
from_big_endian_64 function
The from_big_endian_64 function decodes a bigint from its big-endian binary representation.
Syntax
from_big_endian_64(x)Parameters
|
Parameter |
Description |
|
x |
A varbinary expression. |
Return value type
bigint
Examples
Convert the big-endian binary representation of the number 10 back to a number.
-
Query statement
* | SELECT from_big_endian_64(to_big_endian_64(10)) -
The result is
10.
from_hex function
The from_hex function converts a hexadecimal string into binary data.
Syntax
from_hex(x)Parameters
|
Parameter |
Description |
|
x |
A hexadecimal varchar string. |
Return value type
varbinary
Examples
Convert the hexadecimal string 'D74D' to binary data.
-
Query statement
* | SELECT from_hex('D74D') -
Query results: The value of the
_col0column is100=.
length function
The length function returns the length of varbinary data in bytes or the number of characters in a varchar string.
Syntax
length(x)Parameters
|
Parameter |
Description |
|
x |
A varbinary or varchar expression. |
Return value type
bigint
Examples
Calculate the length of the string '00101000'.
-
Query statement
* | SELECT length('00101000') -
The result is
8.
md5 function
The md5 function computes the MD5 hash of binary data.
Syntax
md5(x)Parameters
|
Parameter |
Description |
|
x |
A varbinary expression. |
Return value type
varbinary
Examples
Compute the MD5 hash of the binary data decoded from the Base64 string '1101'.
-
Query statement
* | SELECT MD5(from_base64('1101')) AS md5 -
Query results: The value of the md5 column is
rG3lOanun9N57opubMnPDw==.
to_base64 function
The to_base64 function encodes binary data into a Base64-encoded string.
Syntax
to_base64(x)Parameters
|
Parameter |
Description |
|
x |
A varbinary expression. |
Return value type
varchar
Examples
Encode the binary data decoded from the Base64 string '10' into a Base64 string.
-
Query statement
* | SELECT to_base64(from_base64('10')) AS base64 -
Query results: The value of the base64 column is
1w==.
to_base64url function
The to_base64url function encodes binary data into a URL-safe Base64 string.
Syntax
to_base64url(x)Parameters
|
Parameter |
Description |
|
x |
A varbinary expression. |
Return value type
varchar
Examples
Encode the binary data decoded from the Base64 string '100' into a URL-safe Base64 string.
-
Query statement
* | SELECT to_base64url(from_base64('100')) -
Query results:
to_hex function
The to_hex function converts binary data into a hexadecimal string.
Syntax
to_hex(x)Parameters
|
Parameter |
Description |
|
x |
A varbinary expression. |
Return value type
varchar
Examples
Convert the binary data decoded from the Base64 string '100' to a hexadecimal string.
-
Query statement
* | SELECT to_hex(from_base64('100')) -
Query results: The value of the
_col0column isD74D.
to_big_endian_64 function
The to_big_endian_64 function encodes a bigint into its big-endian binary representation.
Syntax
to_big_endian_64(x)Parameters
|
Parameter |
Description |
|
x |
A bigint expression. |
Return value type
varbinary
Examples
Convert the number 0 to big-endian binary data.
-
Query statement
* | SELECT to_big_endian_64(0) -
Query results:
sha1 function
The sha1 function computes the SHA-1 hash of binary data.
Syntax
sha1(x)Parameters
|
Parameter |
Description |
|
x |
A varbinary expression. |
Return value type
varbinary
Examples
Compute the SHA-1 hash of the binary data decoded from the Base64 string '1101'.
-
Query statement
* | SELECT sha1(from_base64('1101')) AS sha1 -
Query results:
sha256 function
The sha256 function computes the SHA-256 hash of binary data.
Syntax
sha256(x)Parameters
|
Parameter |
Description |
|
x |
A varbinary expression. |
Return value type
varbinary
Examples
Compute the SHA-256 hash of the binary data decoded from the Base64 string '1101'.
-
Query statement
* | SELECT sha256(from_base64('1101')) AS sha256 -
Query results:
sha512 function
The sha512 function computes the SHA-512 hash of binary data.
Syntax
sha512(x)Parameters
|
Parameter |
Description |
|
x |
A varbinary expression. |
Return value type
varbinary
Examples
Compute the SHA-512 hash of the binary data decoded from the Base64 string '1101'.
-
Query statement
* | SELECT sha512(from_base64('1101')) AS sha512 -
Query results:
xxhash64 function
The xxhash64 function computes the xxHash64 hash of binary data.
Syntax
xxhash64(x)Parameters
|
Parameter |
Description |
|
x |
A varbinary expression. |
Return value type
varbinary
Examples
Compute the xxHash64 hash of the binary data decoded from the Base64 string '10'.
-
Query statement
* | SELECT xxhash64(from_base64('10')) -
Query results:
