Binary Functions and Operators

Binary Operators

|| The operator performs the join.

Binary Functions

• length(binary) → bigint

  Return the byte length of binary.

  select length(x'00141f');-- 3

• concat(binary1, ..., binaryN) → varbinary

  Concatenates binary1, binary2, and binaryN. This function returns the same function as the SQL standard connector ||.

  select concat(X'32335F',x'00141f'); -- 32 33 5f 00 14 1f

• to_base64(binary) → varchar

  Encodes binary to a Base64 character string.

  select to_base64(CAST('hello world' as binary)); -- aGVsbG8gd29ybGQ=

• from_base64(string) → varbinary

  Decode the Base64-encoded string as varbinary.

  select from_base64('helloworld'); --  85 e9 65 a3 0a 2b 95

• unbase64(string) → varbinary

  Decode the Base64-encoded string as varbinary.

  SELECT from_base64('helloworld'); --  85 e9 65 a3 0a 2b 95

• to_base64url(binary) → varchar

  Use URL security characters to encode binary to a base64 character string.

  select to_base64url(x'555555');  -- VVVV

• from_base64url(string) → varbinary

  Use the URL security character to decode the Base64-encoded string into binary data.

  select from_base64url('helloworld'); --  85 e9 65 a3 0a 2b 95

• to_hex(binary) → varchar

  Encode the binary to a hexadecimal string.

  select to_hex(x'15245F');  --  15245F

• from_hex(string) → varbinary

  Decodes a hexadecimal string into binary data.

  select from_hex('FFFF'); --  ff ff

• to_big_endian_64(bigint) → varbinary

  Encodes a number of the bigint type into a 64-bit big-endian complement.

  select to_big_endian_64(1234);
            _col0          
  -------------------------
   00 00 00 00 00 00 04 d2 
  (1 row)

• from_big_endian_64(binary) → bigint

  The binary code in 64-bit big-endian complement format is decoded as a number of the bigint type.

  select from_big_endian_64(x'00 00 00 00 00 00 04 d2');
   _col0 
  -------
    1234 
  (1 row)

• to_big_endian_32(integer) → varbinary

  Encodes a number of the bigint type into a 32-bit big-endian complement.

  select to_big_endian_32(1999);
      _col0    
  -------------
   00 00 07 cf 
  (1 row)

• from_big_endian_32(binary) → integer

  The 32-bit big-endian two's complement format is decoded into a number of the bigint type.

  select from_big_endian_32(x'00 00 07 cf');
   _col0 
  -------
    1999 
  (1 row)

• to_ieee754_32(real) → varbinary

  According to the IEEE 754 algorithm, a single-precision floating-point number is encoded into a 32-bit big-endian binary block.

  select to_ieee754_32(3.14);
      _col0    
  -------------
   40 48 f5 c3 
  (1 row)

• from_ieee754_32(binary) → real

  Decodes the 32-bit big-endian binary in IEEE 754 single-precision floating-point format.

  select from_ieee754_32(x'40 48 f5 c3');
   _col0 
  -------
    3.14 
  (1 row)

• to_ieee754_64(double) → varbinary

  According to the IEEE 754 algorithm, a double-precision floating point number is encoded into a 64-bit big-endian binary block.

  select to_ieee754_64(3.14); 
   _col0          
  -------------------------
   40 09 1e b8 51 eb 85 1f 
  (1 row)

• from_ieee754_64(binary) → double

  Decodes 64-bit big-endian binary in IEEE 754 single-precision floating-point format.

   select from_ieee754_64(X'40 09 1e b8 51 eb 85 1f');
   _col0 
  -------
    3.14 
  (1 row)

• lpad(binary, size, padbinary) → varbinary

  Left-padded binary to adjust byte size using padbinary. If size is less than the length of the binary file, the result will be truncated to size characters. The value of size cannot be negative, and the value of padbinary cannot be empty.

  select lpad(x'15245F', 11,x'15487F') ; -- 15 48 7f 15 48 7f 15 48 15 24 5f

• rpad(binary, size, padbinary) → varbinary

  Right-padded binary to use padbinary to resize bytes. If size is less than the length of the binary file, the result will be truncated to size characters. The value of size cannot be negative, and the value of padbinary cannot be empty.

  SELECT rpad(x'15245F', 11,x'15487F'); -- 15 24 5f 15 48 7f 15 48 7f 15 48

• crc32(binary) → bigint

  Calculate the CRC 32 value of the binary block.

• md5(binary) → varbinary

  Calculates the MD 5 hash value of a binary block.

• sha1(binary) → varbinary

  Calculates the SHA 1 hash value of a binary block.

• sha2(string, integer) → string

  SHA2 is a standard cryptographic hash algorithm used to convert a variable-length string into a string. Its output can be 224 bits, 256 bits, 384 bits, or 512 bits, corresponding to SHA-224, SHA-256, SHA-384 and SHA512, respectively.

• sha256(binary) → varbinary

  Calculates the SHA 256 hash value of a binary block.

• sha512(binary) → varbinary

  Calculates the SHA 512 hash value of a binary block.

• xxhash64(binary) → varbinary

  Calculates the XXHASH 64 hash value of a binary block.

• spooky_hash_v2_32(binary) → varbinary

  Calculates the 32-bit SpookyHashV2 hash value of a binary block.

• spooky_hash_v2_64(binary) → varbinary

  Calculates the 64-bit SpookyHashV2 hash value of a binary block.

• hmac_md5(binary, key) → varbinary

  Use the given key to calculate the HMAC value of the binary block (using MD5).

• hmac_sha1(binary, key) → varbinary

  Use the given key to calculate the HMAC value of the binary block (using SHA1).

• hmac_sha256(binary, key) → varbinary

  Use the given key to calculate the HMAC value of the binary block (using SHA256).

• hmac_sha512(binary, key) → varbinary

  Use the given key to calculate the HMAC value of the binary block (using SHA512).

  

  The CRC32, MD5, and SHA1 algorithms have been found to have vulnerabilities. Do not use them for cryptography.