Base58 functions
Base58 is a binary-to-text encoding scheme that is commonly used in Bitcoin and Solana. It uses an alphabet of 58 characters, which is the same as the Base64 encoding scheme, but omits the characters 0
, O
, I
, and l
to avoid confusion between similar-looking letters and numbers.
You can use the from_base58
function to convert a Base58-encoded string to a VARBINARY
value. For example, the following query converts the Base58-encoded string 3DZBMRwnSU8f
to a VARBINARY
value:
SELECT
from_base58('3DZBMRwnSU8f')
-- results in VARBINARY 0x030094357700000000
Now that we are able to convert Base58-encoded strings to VARBINARY
values, we can use the bytearray_to_bigint
function to convert the VARBINARY
value to a BIGINT
value. However, we need to consider more things:
-
The decoded
VARBINARY
value does not only contain the data we want to convert to aBIGINT
value. It also contains a discriminator that indicates which function of a solana program was called. We need to remove this discriminator before we can convert theVARBINARY
value to aBIGINT
value. We do this by using thebytearray_substring
function. -
Solana uses little-endian byte order, so we need to reverse the byte order before we can convert the
VARBINARY
value to aBIGINT
value. We do this by using thebytearray_reverse
function.
SELECT
bytearray_to_bigint(bytearray_reverse(bytearray_substring(from_base58('3DZBMRwnSU8f'), 2, 8))) as token_sold_amount
-- results in BIGINT 2000000000
In a real-world scenario, here is how you would use these functions to decode a Base58-encoded string that represents a token amount in a Solana swap transaction:
SELECT
sp.call_inner_instructions[1].data
,from_base58(sp.call_inner_instructions[1].data) as first_step
,bytearray_substring(from_base58(sp.call_inner_instructions[1].data), 2, 8) as second_step
,bytearray_reverse(bytearray_substring(from_base58(sp.call_inner_instructions[1].data), 2, 8)) as third_step
,bytearray_to_bigint(bytearray_reverse(bytearray_substring(from_base58(sp.call_inner_instructions[1].data), 2, 8))) as decoded_amount
FROM whirlpool_solana.whirlpool_call_swap sp
WHERE sp.account_whirlpool = '7qbRF6YsyGuLUVs6Y1q64bdVrfe4ZcUUz1JRdoVNUJnm'
AND call_tx_id = '44kmeC1edSfp21K5kKNVViJvLHG8XQqqu3KbHsrYcYZGmopWwBgP48c9u1DRBMGtQcbvyxd2TT8syY7ZvwpHqkhF'
and call_block_slot = 187701147
Functions
from_base58()
from_base58(varchar)
→ varbinary
Converts a Base58-encoded string to a VARBINARY
value.
SELECT
from_base58('3DZBMRwnSU8f')
-- results in VARBINARY 0x030094357700000000
to_base58()
to_base58(varbinary)
→ varchar
Converts a VARBINARY
value to a Base58-encoded string.
SELECT
to_base58(0x030094357700000000)
-- results in base58 encoded varchar 3DZBMRwnSU8f
Was this page helpful?