How to convert Base64 to a comma separated binary

Question

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??

why do you convert base64 to hex, then to bit then to decimal? If you only need decimal, convert it to decimal. — jps, Jun 30 '22 at 21:23
Which RDBMS are you using (MySQL, Postgres, Oracle, SQL Server, something else)? Please add the tag of your database to your question. See [this](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) to know why tagging is important. — Bohemian, Jun 30 '22 at 21:25
Converting directly to decimal is the logical approach. However i do not know how to do it natively. The explaination is to break it down. If there is a native syntax in sql server 2019 then please let me know :) ----And this is for SQL SERVER 2019. Sorry about the missing tag. its been corrected. — TheNewGuy, Jun 30 '22 at 21:29
I'm also a bit confused by your question. You say you *managed to decode it to base64* and then you show hex code. What is the original data in the db-table? How did you decode to base64 (or hex)? A little bit of code would help to understand — jps, Jun 30 '22 at 21:39
So is the "original data" even relevant to the question? It looks like you have a series of binary digits stored as a comma-delimited string that you want to convert to equivalent integer values. That's pretty easy - what output are you after - a comma separated string of integers? A table of integers? something else? This is not clear. — Stu, Jun 30 '22 at 21:49
i'm after a comma separated string of integers. i'm interested in hearing what your thoughts on this solution is — TheNewGuy, Jun 30 '22 at 21:58
I don't see any base64. 00BOA045 ... is hex code. So you need hex to int actually. — jps, Jun 30 '22 at 22:06
@JPS you are right and i was off track. So its actually a series of binary digitals as a comma delimited string that i need to convert. i'm trying to convert the entire string but i think it needs parsed first as the conversion is trying to convert the entire hex. — TheNewGuy, Jun 30 '22 at 22:16
SQL is a poor choice of language to write app code, which is what you really need. So write some code in whatever language you prefer, eg python, and convert the exported data to importable data. — Bohemian, Jun 30 '22 at 23:14

score 1 · Accepted Answer · answered Jun 30 '22 at 22:19

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

This looks incredibly close!!! The missing link that i see is the step where the binary digits stored as a comma-delimited string are converted a structure equivalent to the sample list that you have in your example. I'm referring to '11010011,01000000,01110100,00000011,01001110,00111001,11010011,01000000,01111100' — TheNewGuy, Jun 30 '22 at 22:25
@TheNewGuy I'm not sure I follow, but if you have the source data in a `@variable` just replace the hard-coded string for the variable. This is not really clear from your question, but this can adapt to either a single variable or a table. — Stu, Jun 30 '22 at 22:27

How to convert Base64 to a comma separated binary

1 Answers1