Varbinary functions (DuneSQL)
Varbinary Functions
Dune SQL represents varbinarys using the varbinary type.
To make it simpler to work with varbinarys we have the following helper functions, which work with these two kinds of representation. They simplify interactions with varbinarys, as they automatically account for the 0x
-prefix and use byte index instead of character index. For instance, the varbinary_substring methods take indexes by byte, not by character (twice the varbinary length).
You can use these function to extract data from undecoded events logs, or calldata. For instance, if you have a function that takes a uint256
as an argument, you can use varbinary_to_uint256
to extract the value from the calldata. You’ll oftentimes need to use varbinary_substring
to extract the correct part of the calldata or use other bytearray manipulation functions to get the correct value.
select tx_hash,
data,
varbinary_substring(data,97,32) as fee,
varbinary_to_uint256(varbinary_substring(data,97,32)) as fee_uint256
from optimism.logs
where contract_address = 0xa1ace9ce6862e865937939005b1a6c5ac938a11f
and topic0 = 0xc9d5ada2ea384fe04826ecd1b258955ac73c3e2e20d755108eafde90bc5588d4
-- sample transaction hash
and tx_hash IN (0x3e3c558e7f723e3bb7de1d8f5f920ca206e3e878984296a2b8e6af2969003a19,
0xccfd2033adfb1fdd14fdfc047fe554ba7549e396abc6c559e9528a4259295b89)
Varbinary to Numeric Functions
The varbinary conversion functions throw an overflow exception if the varbinary is larger than the number of bytes supported of the type, even if the most significant bytes are all zero. It is possible to use varbinary_ltrim
in order to trim the zero bytes from the left.
Here is a dashboard with examples covering all of the below functions. For a more comprehensive look at all special DuneSQL functions, use this dashboard from cryptuschrist.
varbinary_to_integer()
varbinary_to_integer(varbinary)
→ integer
Returns the INTEGER
value of a big-endian varbinary of length <= 4 representing the integer in two’s complement. If the varbinary has length < 4 it is padded with zero bytes.
-- convert bytearray to integer [result will be either 1 or 0]
-- if 1 = true, 0 = false
SELECT tx_hash,
varbinary_to_integer(varbinary_ltrim(varbinary_substring(data,1,32))) as isQuote_number,
CASE WHEN varbinary_to_integer(varbinary_ltrim(varbinary_substring(data,1,32))) = 1 THEN TRUE ELSE FALSE END AS isQuote
FROM arbitrum.logs
WHERE contract_address = 0xdaf4ffb05bfcb2c328c19135e3e74e1182c88283
AND topic0 = 0xf1bc206c8d659bf05edd19865dbae82643062168ec3970d9d7c5468f900487d9
LIMIT 10
varbinary_to_bigint()
varbinary_to_bigint(varbinary)
→ bigint
Returns the BIGINT
value of a big-endian varbinary of length <= 8 representing the bigint in two’s complement. If the varbinary has length < 8 it is padded with zero bytes.
-- convert bytearray to integer [result will be either 1 or 0]
-- if 1 = true, 0 = false
SELECT tx_hash,
varbinary_to_bigint(varbinary_ltrim(varbinary_substring(data,1,32))) as isQuote_number,
CASE WHEN varbinary_to_bigint(varbinary_ltrim(varbinary_substring(data,1,32))) = 1 THEN TRUE ELSE FALSE END AS isQuote
FROM arbitrum.logs
WHERE contract_address = 0xdaf4ffb05bfcb2c328c19135e3e74e1182c88283
AND topic0 = 0xf1bc206c8d659bf05edd19865dbae82643062168ec3970d9d7c5468f900487d9
LIMIT 10
varbinary_to_decimal()
varbinary_to_decimal(varbinary)
→ decimal(38,0)
Returns the DECIMAL(38,0)
value of a big-endian varbinary of length <= 16 representing the decimal(38,0) in two’s complement. If the varbinary has length < 16 it is padded with zero bytes.
-- using raw table to get usdc transfers amount
-- transfer topic0 = 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef
-- usdc contract_address = 0xaf88d065e77c8cc2239327c5edb3a432268e5831
SELECT tx_hash,
data,
varbinary_to_decimal(varbinary_ltrim(data)) as data_decimal
FROM arbitrum.logs
WHERE topic0 = 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef
AND contract_address = 0xaf88d065e77c8cc2239327c5edb3a432268e5831
AND block_time >= NOW() - interval '1' day
LIMIT 50
varbinary_to_uint256()
varbinary_to_uint256(varbinary)
→ uint256
Returns the UINT256
of a big-endian varbinary of length <= 32 representing the unsigned integer. If the varbinary has length < 32 it is padded with zero bytes.
select tx_hash,
data,
varbinary_substring(data,97,32) as fee,
varbinary_to_uint256(varbinary_substring(data,97,32)) as fee_uint256
from optimism.logs
where contract_address = 0xa1ace9ce6862e865937939005b1a6c5ac938a11f
and topic0 = 0xc9d5ada2ea384fe04826ecd1b258955ac73c3e2e20d755108eafde90bc5588d4
-- sample transaction hash
and tx_hash IN (0x3e3c558e7f723e3bb7de1d8f5f920ca206e3e878984296a2b8e6af2969003a19,
0xccfd2033adfb1fdd14fdfc047fe554ba7549e396abc6c559e9528a4259295b89)
varbinary_to_int256()
varbinary_to_int256(varbinary)
→ int256
Returns the INT256
of a big-endian varbinary of length <= 32 representing the signed integer. If the varbinary has length < 32 it is padded with zero bytes.
select tx_hash,
data,
varbinary_substring(data,65,32) as sizeDelta,
varbinary_to_int256(varbinary_substring(data,65,32)) as sizeDelta_int256
from optimism.logs
where contract_address = 0xa1ace9ce6862e865937939005b1a6c5ac938a11f
and topic0 = 0xc9d5ada2ea384fe04826ecd1b258955ac73c3e2e20d755108eafde90bc5588d4
-- sample transaction hash
and tx_hash IN (0x3e3c558e7f723e3bb7de1d8f5f920ca206e3e878984296a2b8e6af2969003a19,
0xccfd2033adfb1fdd14fdfc047fe554ba7549e396abc6c559e9528a4259295b89)
bytea2numeric()
bytea2numeric(varbinary)
→ bigint
This function has been deprecated. It is an alias for varbinary_to_bigint
.
Varbinary to text
from_utf8()
from_utf8(varbinary)
→ varchar
Converts a varbinary to a string using the UTF-8 encoding.
Select from_utf8(0x48656c6c6f20576f726c64)
-- returns "Hello World"
most commonly will have to be used like this:
Select from_utf8(varbinary_ltrim(0x0000000000000000006e66746e657264732e6169))
Text to varbinary
from_hex()
from_hex(varchar)
→ varbinary
Converts a varbinary expression in datatype string
to varbinary
datatype
Select from_hex('0x6574686275696c646572')
-- returns VARBINARY 0x6574686275696c646572
cast(x as varbinary)
in these cases as it will actually encode the string as varbinary which is different from converting the expression to varbinary
. Varbinary Manipulation Functions
varbinary_concat()
varbinary_concat(varbinary, varbinary)
→ varbinary
Concatenates two varbinarys or strings.
SELECT varbinary_concat(0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2,
0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48)
varbinary_concat(varchar, varchar)
→ varchar
Concatenates two varbinarys or strings.
-- concatenate varchar
-- using typeof to check the datatype
SELECT varbinary_concat('0xabcd', '0x00ab') as varbinary_varchar_concat,
typeof(varbinary_concat('0xabcd', '0x00ab')) as varbinary_varchar_type
varbinary_length()
varbinary_length(varbinary)
→ bigint
Returns the length of a varbinary.
-- this will return 20
SELECT varbinary_length(0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2)
varbinary_length(varchar)
→ bigint
Returns the length of a string.
-- this will return 20
SELECT typeof('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') as data_type,
varbinary_length('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') varchar_length
varbinary_ltrim()
varbinary_ltrim(varbinary)
→ varbinary
Removes zero bytes or spaces from the beginning of a varbinary
-- this will remove the zeros at the front,
-- returning 0xa2b80f9c09558945800ddf4f8786dcc8b1c44974
SELECT varbinary_ltrim(0x000000000000000000000000a2b80f9c09558945800ddf4f8786dcc8b1c44974)
varbinary_ltrim(varchar)
→ varchar
Removes spaces from the beginning of a string.
-- this will remove the zeros at the front,
-- returning 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2
SELECT typeof('0x0000000000000000000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') as data_type,
varbinary_ltrim('0x0000000000000000000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') varchar_ltrim
varbinary_rtrim()
varbinary_rtrim(varbinary) or varbinary_rtrim(varchar)
→ varbinary or varchar
Removes zero bytes or spaces from the end of a varbinary or string.
-- this will remove the zeros at the end,
-- returning 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2
SELECT varbinary_ltrim(0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc20000000000000000000000000000000000000000)
varbinary_position()
varbinary_position(varbinary, varbinary)
→ bigint
Returns the index of the first occurrence of a given bytearray or string (or 0 if not found) within a varbinary or string.
-- get $ARKM claimers
SELECT * FROM ethereum.transactions
WHERE block_time >= TIMESTAMP '2023-07-17' -- claim start date
AND varbinary_position(data,0x3d13f874) = 1 -- 0x3d13f874 is the methodID
AND success
LIMIT 100
varbinary_position(varchar, varchar)
→ bigint
Returns the index of the first occurrence of a given bytearray or string (or 0 if not found) within a varbinary or string.
-- search for '0x6cc2' and return its position
SELECT varbinary_position('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2','0x6cc2')
varbinary_replace()
varbinary_replace(varbinary, varbinary, varbinary)
→ varbinary
Greedily replaces occurrences of a pattern within a varbinary.
-- replacing the blackhole address (ETH)
-- to WETH address
SELECT varbinary_replace(0x0000000000000000000000000000000000000000,
0x0000000000000000000000000000000000000000,
0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2)
varbinary_replace(varchar, varchar, varchar)
→ varchar
Greedily replaces occurrences of a pattern within a string.
SELECT varbinary_replace('0x0000000000000000000000000000000000000000a2b80f9c09558945800ddf4f8786dcc8b1c44974',
'0x0000000000000000000000000000000000000000',
'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')
varbinary_reverse()
varbinary_reverse(varbinary)
→ varbinary
Reverses a given varbinary.
SELECT varbinary_reverse(0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2)
varbinary_reverse(varchar)
→ varchar
Reverses a given string.
-- '0xabcdef' as original
-- '0xefcdab' as reversed
SELECT '0xabcdef' as original,
varbinary_reverse('0xabcdef') as reversed
varbinary_starts_with()
varbinary_starts_with(varbinary, varbinary)
→ boolean
Determines whether a varbinary starts with a prefix.
-- get $ARKM claimers
-- varbinary_starts_with checks whether
-- data starts with 0x3d13f874 (claim methodID)
SELECT * FROM ethereum.transactions
WHERE block_time >= TIMESTAMP '2023-07-17'
AND varbinary_starts_with(data,0x3d13f874) -- returns true if starts with 0x3d13f874
AND success
LIMIT 100
varbinary_starts_with(varchar, varchar)
→ boolean
Determines whether a string starts with a prefix.
-- returns true if starts with 0x3d13f874
SELECT varbinary_starts_with(0x3d13f874abcd,0x3d13f874)
varbinary_substring()
varbinary_substring(varbinary, integer)
→ varbinary
Returns a suffix varbinary or string starting at a given index.
-- using varbinary_substring starting from the 21th index
-- this returns 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2
SELECT 0x0000000000000000000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 as original_bytearray,
varbinary_substring(0x0000000000000000000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2,21) as bytearraysubstring_data
varbinary_substring(varchar, integer)
→ varchar
Returns a suffix string starting at a given index.
Returns a sub varbinary or string of a given length starting at an index.
-- getting the sizeDelta using varbinary_substring
-- and converting to uint256 using varbinary_to_uint256
SELECT tx_hash,
varbinary_substring(data,65,32) as sizeDelta,
varbinary_to_uint256(varbinary_substring(data,65,32)) as sizeDelta_uint256
FROM optimism.logs
WHERE contract_address = 0xa1ace9ce6862e865937939005b1a6c5ac938a11f
AND topic0 = 0xc9d5ada2ea384fe04826ecd1b258955ac73c3e2e20d755108eafde90bc5588d4
-- some sample transaction hash
AND tx_hash IN (0x3e3c558e7f723e3bb7de1d8f5f920ca206e3e878984296a2b8e6af2969003a19,
0xccfd2033adfb1fdd14fdfc047fe554ba7549e396abc6c559e9528a4259295b89)
varbinary_substring(varchar, integer, integer)
→ varchar
Returns a sub string of a given length starting at an index.
-- returns '0xabcd'
SELECT '0xabcdefabcdef' as varchar_data,
varbinary_substring('0xabcdefabcdef',1,3) as varchar_substring
varbinary_substring(varbinary, integer, integer)
→ varbinary
-- returns 0xabcd
SELECT 0xabcdefabcdef as varbinary_data,
varbinary_substring(0xabcdefabcdef,1,3) as varbinary_substring
Was this page helpful?