I'm modifying the explaination so its clearer. I had this data in an image column within sql server. I used then used this query to get these results And using online converters i can get it to a binary/commma separated list. From here i need to convert each 8bit word into a decimal. The new list doesn't need to show

How to convert Base64 to a comma separated binary

I’m modifying the explaination so its clearer.

I had this data in an image column within sql server.

This is the original data
 0x00B0A04500B898450070954500A89345006893450028944500109545005095450088944500D8924500D0904500288F4500608E4500A08E4500988F4500D0904500B091450000924500C09145001891450040904500788F4500D88E4500608E4500108E4500F88D4500F88D4500008E4500F88D4500E88D4500108E4500C08E45004090450038924500B0934500E8924500C08D4500A882450000624500A0334500C0FD44008093440080CC43000069C300403AC400A090C40060BCC40080E3C4002003C500E010C500D019C500901DC500B01DC500A01CC500901CC500401EC500F01FC500601EC500A015C5004003C50060CEC4006087C40000E7C30000134300C02E44000092440040C24400A0E94400C004450090114500901B45004023450040294500602E450030334500F0374500203C4500603F4500404145001042450060424500104345008044450050464500B0474500E0474500A0464500C044450020444500F0464500F04E4500E05C45000070450018834500388E4500E8974500E89E450098A24500F8A24500B0A0459AD90443AE870543B85E0543E1BA044348A10343333302437B94004348E1FD4214AEFA420080F7429A19F442713DF0428FC2EB427B94E64229DCE042CDCCDA427B94D442713DCE42AEC7C7429A19C142B81EBA42CDCCB242B81EAB427B14A34214AE9A4285EB91420AD7884285EB7E428FC26B42713D5842713D44425C8F2F4200001A428FC20342C3F5DA41C3F5B041D7A38C41AE47614100003C411F852741A4701D416666164114AE0B41CDCCF440295CC74052B8964014AE574014AE1740EC51D83F3D0A973FEC51383F295C8F3E00000000AE47E13D3D0A573F52B80E400AD78340B81EC540000000417B141641D7A324413D0A2F415C8F3A4100004C4148E1664100008641AE479D416666B84152B8D6410000F84100000E42F6282142713D3542CDCC49429A995E421F857342CD4C84421F058F4285EB994266E6A4428FC2AF42AE47BA420A57C44285EBCD42D723D742B81EE0420AD7E842F628F14214AEF842E1FAFE4248E10143146E0343C3350443F66804431F4504433D0A044314EE03433D0A0443486104439AD90443

I used then used this query to get these results

Select CONVERT(VARCHAR(MAX),(CONVERT(varbinary(MAX), 'the image column')) ,2) from [thetable]



00B0A04500B898450070954500A89345006893450028944500109545005095450088944500D8924500D0904500288F4500608E4500A08E4500988F4500D0904500B091450000924500C09145001891450040904500788F4500D88E4500608E4500108E4500F88D4500F88D4500008E4500F88D4500E88D4500108E4500C08E45004090450038924500B0934500E8924500C08D4500A882450000624500A0334500C0FD44008093440080CC43000069C300403AC400A090C40060BCC40080E3C4002003C500E010C500D019C500901DC500B01DC500A01CC500901CC500401EC500F01FC500601EC500A015C5004003C50060CEC4006087C40000E7C30000134300C02E44000092440040C24400A0E94400C004450090114500901B45004023450040294500602E450030334500F0374500203C4500603F4500404145001042450060424500104345008044450050464500B0474500E0474500A0464500C044450020444500F0464500F04E4500E05C45000070450018834500388E4500E8974500E89E450098A24500F8A24500B0A0459AD90443AE870543B85E0543E1BA044348A10343333302437B94004348E1FD4214AEFA420080F7429A19F442713DF0428FC2EB427B94E64229DCE042CDCCDA427B94D442713DCE42AEC7C7429A19C142B81EBA42CDCCB242B81EAB427B14A34214AE9A4285EB91420AD7884285EB7E428FC26B42713D5842713D44425C8F2F4200001A428FC20342C3F5DA41C3F5B041D7A38C41AE47614100003C411F852741A4701D416666164114AE0B41CDCCF440295CC74052B8964014AE574014AE1740EC51D83F3D0A973FEC51383F295C8F3E00000000AE47E13D3D0A573F52B80E400AD78340B81EC540000000417B141641D7A324413D0A2F415C8F3A4100004C4148E1664100008641AE479D416666B84152B8D6410000F84100000E42F6282142713D3542CDCC49429A995E421F857342CD4C84421F058F4285EB994266E6A4428FC2AF42AE47BA420A57C44285EBCD42D723D742B81EE0420AD7E842F628F14214AEF842E1FAFE4248E10143146E0343C3350443F66804431F4504433D0A044314EE03433D0A0443486104439AD90443

And using online converters i can get it to a binary/commma separated list.

11010011,01000000,01110100,00000011,01001110,00111001,11010011,01000000,01111100,11110111,11001110,00111001,11010011,01001110,11110100,11110111,10011110,00111001,11010011,01000000,00111100,11110111,01111110,00111001,11010011,01001110,10111100,11110111,01111110,00111001,11010011,01001101,10111100,11110111,10001110,00111001,11010011,01001101,01110100,11110111,10011110,00111001,11010011,01001110,01110100,11110111,10011110,00111001,11010011,01001111,00111100,11110111,10001110,00111001,11010011,01000000,11111100,11110111,01101110,00111001,11010011,01000000,11110100,11110111,01001110,00111001,11010011,01001101,10111100,11110000,01011110,00111001,11010011,01001110,10110100,11110000,01001110,00111001,11010011,01000000,00110100,11110000,01001110,00111001,11010011,01001111,01111100,11110000,01011110,00111001,11010011,01000000,11110100,11110111,01001110,00111001,11010011,01000000,01110100,11110111,01011110,00111001,11010011,01001101,00110100,11110111,01101110,00111001,11010011,01000000,10110100,11110111,01011110,00111001,11010011,01001101,01111100,11110111,01011110,00111001,11010011,01001110,00110100,11110111,01001110,00111001,11010011,01001110,11111100,11110000,01011110,00111001,11010011,01000000,11111100,11110000,01001110,00111001,11010011,01001110,10110100,11110000,01001110,00111001,11010011,01001101,01110100,11110000,01001110,00111001,11010011,01000001,01111100,11110000,00111110,00111001,11010011,01000001,01111100,11110000,00111110,00111001,11010011,01001101,00110100,11110000,01001110,00111001,11010011,01000001,01111100,11110000,00111110,00111001,11010011,01000001,00111100,11110000,00111110,00111001,11010011,01001101,01110100,11110000,01001110,00111001,11010011,01000000,10110100,11110000,01001110,00111001,11010011,01001110,00110100,11110111,01001110,00111001,11010011,01001101,11111100,11110111,01101110,00111001,11010011,01000000,01110100,11110111,01111110,00111001,11010011,01000001,00111100,11110111,01101110,00111001,11010011,01000000,10110100,11110000,00111110,00111001,11010011,01000000,00111100,11110011,01101110,00111001,11010011,01001101,00110100,11101011,01101110,00111001,11010011,01000000,00110100,11011111,01111110,00111001,11010011,01000000,10110100,00010100,00111110,00111000,11010011,01001111,00110100,11110111,01111110,00111000,11010011,01001111,00110100,00001000,00101110,00110111,11010011,01001101,00110100,11101011,11010000,10110111,11010011,01001110,00110100,11011100,00000000,10111000,11010011,01000000,00110100,11110111,01000000,10111000,11010011,01001110,10110100,00000100,00100000,10111000,11010011,01001111,00110100,00010011,01110000,10111000,11010011,01001101,10110100,11010011,01110000,10111001,11010011,01000001,00110100,11010111,01000000,10111001,11010011,01000000,11110100,11010111,11010000,10111001,11010011,01001111,01110100,11010100,00110000,10111001,11010011,01000000,01110100,11010100,00110000,10111001,11010011,01000000,00110100,11010100,00100000,10111001,11010011,01001111,01110100,11010100,00100000,10111001,11010011,01001110,00110100,11010100,01000000,10111001,11010011,01000001,01110100,11010100,01010000,10111001,11010011,01001110,10110100,11010100,01000000,10111001,11010011,01000000,00110100,11010111,10010000,10111001,11010011,01001110,00110100,11010011,01110000,10111001,11010011,01001110,10110100,00001000,01000000,10111000,11010011,01001110,10110100,11110011,10110000,10111000,11010011,01001101,00110100,00010011,10110000,10110111,11010011,01001101,00110100,11010111,01111110,00110111,11010011,01000000,10110100,11011000,01001110,00111000,11010011,01001101,00110100,11110111,01101110,00111000,11010011,01001110,00110100,00001011,01101110,00111000,11010011,01000000,00110100,00010011,11011110,00111000,11010011,01000000,10110100,11010011,10001110,00111001,11010011,01001111,01110100,11010111,01011110,00111001,11010011,01001111,01110100,11010100,00011110,00111001,11010011,01001110,00110100,11011011,01111110,00111001,11010011,01001110,00110100,11011011,11011110,00111001,11010011,01001110,10110100,11011000,01001110,00111001,11010011,01001101,11110100,11011111,01111110,00111001,11010011,01000001,01110100,11011111,10111110,00111001,11010011,01001101,10110100,11011100,00101110,00111001,11010011,01001110,10110100,11011100,01011110,00111001,11010011,01001110,00110100,11100011,01011110,00111001,11010011,01001101,01110100,11100011,01101110,00111001,11010011,01001110,10110100,11100011,01101110,00111001,11010011,01001101,01110100,11100011,01111110,00111001,11010011,01001111,00110100,11100011,10001110,00111001,11010011,01001110,01110100,11100011,10101110,00111001,11010011,01000000,01110100,11100011,10111110,00111001,11010011,01000001,00110100,11100011,10111110,00111001,11010011,01000000,00110100,11100011,10101110,00111001,11010011,01000000,10110100,11100011,10001110,00111001,11010011,01001101,10110100,11100011,10001110,00111001,11010011,01000001,01110100,11100011,10101110,00111001,11010011,01000001,01110100,11100000,01001110,00111001,11010011,01000001,00110100,11100100,00101110,00111001,11010011,01001101,00110100,11101111,01001110,00111001,11010011,01001101,01111100,11110011,01111110,00111001,11010011,01001101,11111100,11110000,01001110,00111001,11010011,01000001,00111100,11110111,10111110,00111001,11010011,01000001,00111100,11110100,01001110,00111001,11010011,01001111,01111100,00000011,01101110,00111001,11010011,01000001,01111100,00000011,01101110,00111001,11010011,01000000,01110100,00000011,01001110,00111001,11110100,00000000,11111101,11010011,10001110,00110111,00000000,01001111,00111011,11010011,10011110,00110111,00000111,11001110,01000100,11010011,10011110,00110111,00010011,01010000,01000000,11010011,10001110,00110111,11100011,11000000,00110101,11010011,01111110,00110111,11011111,01111101,11110111,11010011,01101110,00110111,11101100,00011111,01111000,11010011,01001110,00110111,11100011,11000001,00110101,00010100,00111110,00110110,11010111,10000000,00000100,00010100,00001110,00110110,11010011,01001111,00110100,00010111,10111110,00110110,11110100,00001101,01111101,00010111,10001110,00110110,11101111,01011101,11000011,00010111,01001110,00110110,11110000,01010000,10110110,00010000,00011110,00110110,11101100,00011111,01111000,00010011,10101110,00110110,11011011,11010000,11000010,00010011,01001110,00110110,00001000,00110000,10000010,00001100,00001110,00110110,11101100,00011111,01111000,00001111,10001110,00110110,11101111,01011101,11000011,00001000,01001110,00110110,00000000,01000000,10111011,00001011,10111110,00110110,11110100,00001101,01111101,00001011,01011110,00110110,00000111,11001101,01000100,00000100,00001110,00110110,00001000,00110000,10000010,00000111,01101110,00110110,00000111,11001101,01000100,00000000,00011110,00110110,11101100,00011101,01111000,00000011,01111110,00110110,11010111,10000000,00000100,11110100,00001110,00110110,11110011,10010001,00000001,11110111,01011110,00110110,11010000,00000000,11111011,11110011,11001110,00110110,11110011,10010001,00000001,11101100,01001110,00110110,11110000,01010000,10110110,11101000,00011110,00110110,11101111,01011101,11000011,11100111,11001110,00110110,11101111,01011101,11000011,11100011,10001110,00110110,11100100,00101111,00000101,11011000,01011110,00110110,11010011,01001101,00110100,11010100,00001110,00110110,11110000,01010000,10110110,11010011,01111110,00110110,00001011,01110001,01111001,00001100,00001110,00110101,00001011,01110001,01111001,00000111,01001110,00110101,00001111,10110000,00110111,11110000,00101110,00110101,00000000,01001110,00111011,11101011,01011110,00110101,11010011,01001101,00110100,11011100,00101110,00110101,11010100,01011111,00111001,11011011,10111110,00110101,00000011,10001110,11110100,11010100,00111110,00110101,11101011,10101110,10111010,11010111,10101110,00110101,11010111,10000000,00000100,11010000,00011110,00110101,00001000,00110000,10000010,00010111,10001110,00110100,11011011,11011110,01000010,00001011,10111110,00110100,11100111,01100000,01111100,11110111,10101110,00110100,11010111,10000000,00000100,11100111,10111110,00110100,11010111,10000000,00000100,11010111,10111110,00110100,00010000,00101110,01110101,00001111,11001101,11000101,11011100,00111101,00000000,11110111,10111101,11000101,00010000,00101110,01110101,11011111,11001101,11000101,11011011,11011110,01000010,11110000,01011101,11000100,11010011,01001101,00110100,11010011,01001101,00110100,00000000,01001110,00111011,00010011,01011101,11000011,11011100,00111101,00000000,11100111,10111101,11000101,11100111,01100000,01111100,11010000,01001110,00110100,11010000,00000000,11111011,11110011,01111110,00110100,00000111,11001101,01000100,00001011,10011110,00110100,11010011,01001101,00110100,11010011,01001110,00110101,11101100,00011101,01111000,11010111,10101110,00110101,00001111,10110000,00110111,11011011,10001110,00110101,11011100,00111101,00000000,11011000,01011110,00110101,11100100,00101111,00000101,11011100,00001110,00110101,11010011,01001101,00110100,11100000,00101110,00110101,11100011,11000001,00110101,11101011,10101110,00110101,11010011,01001101,00110100,11110011,10101110,00110101,00000000,01001110,00111011,11110100,00111110,00110101,11101011,10101110,10111010,00000111,11001110,00110101,11100111,01100000,01111100,00001111,10101110,00110101,11010011,01001101,00110100,00010111,11001110,00110101,11010011,01001101,00110100,11010000,01001110,00110110,00010111,10101101,10111100,11011011,01011110,00110110,11101111,01011101,11000011,11011111,10011110,00110110,00001000,00110000,10000010,11100011,11011110,00110110,11110100,00001111,01111101,11100100,01001110,00110110,11010100,01011111,00111001,11101111,01111110,00110110,00001000,00111110,00000010,11110011,10001110,00110110,11010100,01011101,00111001,11110000,01011110,00110110,11110011,10010001,00000001,11110111,11011110,00110110,11101011,10100001,00111010,00000011,10001110,00110110,11110000,01010000,10110110,00000000,01011110,00110110,00000000,01001110,00111011,00000100,00001110,00110110,11010000,00001110,01111011,00001011,10001110,00110110,11110011,10010001,00000001,00001000,00111110,00110110,00001111,10111101,10110111,00001111,10111110,00110110,00000111,11001101,01000100,00010011,01001110,00110110,11010000,00000000,11111011,00010011,11001110,00110110,00010111,10101101,10111100,00010111,01011110,00110110,11010111,10000000,00000100,00010111,11001110,00110110,00010011,01010001,01000000,00010100,01001110,00110110,11100011,11000001,00110101,11010011,01011110,00110111,11010111,10001110,10000100,11010011,01111110,00110111,00001011,01111101,11111001,11010011,10001110,00110111,00010111,10101110,10111100,11010011,10001110,00110111,11010100,01011110,00111001,11010011,10001110,00110111,11011100,00111101,00000000,11010011,10001110,00110111,11010111,10000001,00000100,11010011,01111110,00110111,11011100,00111101,00000000,11010011,10001110,00110111,11100011,11001110,10110101,11010011,10001110,00110111,11110100,00000000,11111101,11010011,10001110,00110111

From here i need to convert each 8bit word into a decimal.

11010011 = 211, 01000000 = 64, ..........

The new list doesn’t need to show the original 8 bits. Its only there for more detail

Where i am struggling is how to convert it natively within SQL since this is a fairly large table. My attempts have been a bunch of flops and i have hit a mental block. Any help is appreciated.

—–update—–

so what i have learnt is that i have is a hex string of comma separated values that need converted to their integer equivalent. I have tried to convert hex to in but it appears to be trying to convert the entire string at once.

SELECT CONVERT(INT, CONVERT(VARBINARY, '00B0A04500B898450070954500A89345006893450028944500109545005095450088944500D8924500D0904500288F4500608E4500A08E4500988F4500D0904500B091450000924500C09145001891450040904500788F4500D88E4500608E4500108E4500F88D4500F88D4500008E4500F88D4500E88D4500108E4500C08E45004090450038924500B0934500E8924500C08D4500A882450000624500A0334500C0FD44008093440080CC43000069C300403AC400A090C40060BCC40080E3C4002003C500E010C500D019C500901DC500B01DC500A01CC500901CC500401EC500F01FC500601EC500A015C5004003C50060CEC4006087C40000E7C30000134300C02E44000092440040C24400A0E94400C004450090114500901B45004023450040294500602E450030334500F0374500203C4500603F4500404145001042450060424500104345008044450050464500B0474500E0474500A0464500C044450020444500F0464500F04E4500E05C45000070450018834500388E4500E8974500E89E450098A24500F8A24500B0A0459AD90443AE870543B85E0543E1BA044348A10343333302437B94004348E1FD4214AEFA420080F7429A19F442713DF0428FC2EB427B94E64229DCE042CDCCDA427B94D442713DCE42AEC7C7429A19C142B81EBA42CDCCB242B81EAB427B14A34214AE9A4285EB91420AD7884285EB7E428FC26B42713D5842713D44425C8F2F4200001A428FC20342C3F5DA41C3F5B041D7A38C41AE47614100003C411F852741A4701D416666164114AE0B41CDCCF440295CC74052B8964014AE574014AE1740EC51D83F3D0A973FEC51383F295C8F3E00000000AE47E13D3D0A573F52B80E400AD78340B81EC540000000417B141641D7A324413D0A2F415C8F3A4100004C4148E1664100008641AE479D416666B84152B8D6410000F84100000E42F6282142713D3542CDCC49429A995E421F857342CD4C84421F058F4285EB994266E6A4428FC2AF42AE47BA420A57C44285EBCD42D723D742B81EE0420AD7E842F628F14214AEF842E1FAFE4248E10143146E0343C3350443F66804431F4504433D0A044314EE03433D0A0443486104439AD90443', 2))

results in

-1790640048

How do I parse the hex first then do the conversion??

Answer

Given a string of comma-delimited binary digits, one approach is to split the string into rows to get each binary value and then convert each to an integer, then re-aggregate into a delimited string.

First it would be useful to have a function to convert to an integer, one such function could use a simple tally table to split each digit into a row and then sum the bits raised to the correct power of 2.

create function BinaryToInt(@bin varchar(8))
returns table as
return
with seq(n) as (select n from (values (1), (2), (3), (4), (5), (6), (7), (8))t(n)),
bin as (
    select Cast(Substring(Reverse(@bin), n, 1) as int) v, n
    from seq
    where n <= Len(@bin)
)
select Sum(Iif((v = 1), Power(2, n-1), 0)) [Result]
from bin;
go

And then to make use of it by splitting the source string (using OpenJson to ensure correct ordering)

with sampledata as (
  select '11010011,01000000,01110100,00000011,01001110,00111001,11010011,01000000,01111100' bin
)
select String_Agg(Result, ',') within group (order by seq)
from sampledata
cross apply (
select j.[value], Convert(tinyint, j.[key]) Seq 
  from OpenJson(Concat('["', replace(bin, ',', '","'), '"]')) j
)j
cross apply dbo.BinaryToInt(j.[value]);

See Demo Fiddle

Sample data: 11010011,01000000,01110100,00000011,01001110,00111001,11010011,01000000,01111100

Output

Advertisement

Answer