Binary to Decimal via STRING_SPLIT

2018-11-14 5 Comments

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'

5 comments:

  1. Interesting.
    For the number of statements, you could take advantage of the nice relationship between binary and hex. Left-pad extra zeros so length is a multiple of 4, use 16x replace statements e.g. REPLACE(@bin, '0001','x1') then clean up with replace(@bin,'x','').

    ReplyDelete
  2. stupid question, but why did you use STRING_SPLIT instead of SUBSTRING, which fits much more for it?

    ReplyDelete
    Replies
    1. Hi Thomas,
      The use of STRING_SPLIT was mostly an experiment...for fun. I wanted to see if I could use a single operation that was as close to a set-based operation as possible. SUBSTRING use is simple enough, but it requires looping, iterative operations. Thanks for dropping by!

      Delete
  3. Fun factor or proove of concept is ok, but there is absolut no reason, why you have to use a loop or other iterative operations (Cursor etc.) instead of a set based approach for SUBSTRING():

    WITH cte AS (
    SELECT CAST(m.v * 8 + n.v + 1 AS BIGINT) AS pos
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7)) m(v)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7)) n(v)
    )
    SELECT SUM(POWER(IIF(SUBSTRING(t.bin_str, cte.pos, 1) = '1', CAST(2 AS BIGINT), 0), cte.pos)) AS dez,
    CONVERT(VARBINARY(8), SUM(POWER(IIF(SUBSTRING(t.bin_str, cte.pos, 1) = '1', CAST(2 AS BIGINT), 0), cte.pos))) AS hex
    FROM (VALUES ('0000000000001000000000000000000000000010')) AS t(bin_str)
    CROSS JOIN cte

    ReplyDelete
    Replies
    1. Nice solution! Would you consider writing a blog post about it? Steve Jones has written one on the same topic too: http://www.sqlservercentral.com/articles/T-SQL/180361/

      Delete