Is there an elegant way to convert a binary string to its decimal value in Kusto? Preferably in the form of a scalar function, since the data I'm working with has multiple binary string columns, and all with different lengths.
I came up with two methods. The first is as follows: I mv-expand the string so it creates one row per character, I add a row number, and combine it with the binary string length to determine the bit value, then take the power of 2 of that value, and sum over all values by binary string:
let T = datatable(binary:string)
[
"01000001000011010000001000100101",
"11001001001000100111",
"1010",
"11"
];
T
| mv-expand bit = extract_all('(.)', binary) // One character per row
| order by binary asc // Serialize
| extend power = strlen(binary) - 1 - row_number(0, prev(binary) != binary)
| extend decimalValue = bit * pow(2, power) // strlen and row number determine the bit's value
| summarize BinaryToDecimal = sum(decimalValue) by binary
This only works if all binary values are unique, but can be modified by adding some constraints to the row number restart. But like I said, I have to work with a bunch of different binary string columns, so there's gonna be a lot of typing and that's harder to maintain.
The second one extracts all characters in the string to an array, and reverses it to determine bit value. Then mv-apply to multiply each bit with pow(2, array index), and sum over all values.
let T = datatable(binary:string)
[
"01000001000011010000001000100101",
"11001001001000100111",
"1010",
"11"
];
T
| extend bit = extract_all('(.)', binary)
| project binary, reversed = array_reverse(extract_all('(.)', binary))
| mv-apply reversed on (summarize DecimalValue = sum(toint(reversed) * pow(2, row_number(0))))
Is there a way to do this as a function?
Scalar function.
Based on arrays manipulation.
Ugly, but works
let T = datatable(binary:string)
[
"01000001000011010000001000100101",
"11001001001000100111",
"1010",
"11"
];
let F = (binary:string)
{
let powersOf2 = toscalar(range i from 50 to 0 step -1 | summarize make_list(tolong(pow(2, i))));
series_dot_product(parse_json(replace_string(tostring(extract_all('(.)', binary)),'"','')) , array_slice(powersOf2, -strlen(binary), -1))
};
T
| extend DecimalValue = F(binary)
binary | DecimalValue |
---|---|
01000001000011010000001000100101 | 1091371557 |
11001001001000100111 | 823847 |
1010 | 10 |
11 | 3 |