Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

Binary to Decimal via STRING_SPLIT

Colin Stasiuk posed an interesting question on Twitter recently:

I started pondering it for a bit and began to wonder if I could use the new for SQL Server 2016 STRING_SPLIT function to convert a binary string to decimal. The thought process was to split the string into rows of CHAR(1) values, along with an in-string character position. Then I could take the "1" values, calculate the 2แตก value, and sum them up for a decimal value.

I quickly realized I'd need to reverse the binary string. I also discovered the STRING_SPLIT function eliminated 0's adjacent to 1's. I don't have much use for this code at the moment, but maybe there's something here that you can take away.


One huge caveat: STRING_SPLIT does not guarantee the order for rows returned...bummer.


--Original binary string value.
DECLARE @Binary VARCHAR(MAX) = '1000000000000000000000000010'

--Reverse the binary string. "Add in" extra zeros for each one.
--STRING_SPLIT's behavior makes this necessary.
DECLARE @BinaryAltered VARCHAR(MAX) = REVERSE(REPLACE(@Binary, '1', '01'))

;
WITH BaseTwo AS
(
--ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) to generate a natural row number.
SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Exponent
--NOTE: STRING_SPLIT() return order is not guaranteed. This would
--never be supported in production.
FROM STRING_SPLIT(@BinaryAltered, '0')
)
SELECT
@Binary AS [Binary],
SUM(POWER (CAST(2 AS BIGINT), Exponent - 1 ) ) AS [Decimal],
CONVERT(VARBINARY(8), SUM(POWER (CAST(2 AS BIGINT), Exponent - 1 ) )) AS [Hex]
FROM BaseTwo bt
WHERE bt.value = '1'

Comments

Post comment