en-UShe-IL
You are here:  Blog

Awared MVP 

Microsoft® Community Contributor 


Microsoft® Community Contributor


Blog Archive:

הגדל
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

הגדל
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

הגדל
* Can be used in order to search for blogs by keywords

TNWikiSummit


הגדל מה בעמוד?

דצמ1

Written by: ronen ariely
01/12/2016 07:03 RssIcon

the language Transact-SQL does not include built in function to convert binary type into base64 string and vice versa, but it does includes several built-in options we can use. Let's follow some of these options.

 Table Of Content
 Preparation
 Convert VARBINARY to Base64 String
     Converting Variables from VARBINARY to Base64
         Option 1: Convert binary to Base64 using JSON
         Option 2: Convert binary to Base64 using XML XQuery
         Option 3: Convert binary to Base64 using XML and the hint "for xml path"
     Converting Column values from VARBINARY to Base64
         Option 1: Convert binary to Base64 using JSON
         Option 2: Convert binary to Base64 using XML XQuery
         Option 3: Convert binary to Base64 using XML and the hint "for xml path"
 Convert Base64 String to VARBINARY
     Converting Variables from Base64 to VARBINARY
     Converting column values from Base64 to VARBINARY
 Conclusion
 More information
 


Preparation

1. Create folder C:\Ariely_BASE46_VARCHAR

2. insert image

we will need a simple image for our demo. In this demo I will use my avater image from my MSDN profile. You can go to your profile -> right click on your avatar image -> and save the image in the new folder.

* For the sake of this demo, if you use different image, then name it avatar.jpg

3. Create new table

USE tempdb
GO
 
DROP TABLE IF EXISTS AriTestTbl
GO
 
CREATE TABLE AriTestTbl(
    Id int identity(1,1) primary key,
    AvatarBinary VARBINARY(MAX),
    AvatarBase64 VARCHAR(MAX)
)
GO

4. Import the image into the database as binary data

* I will insert the same image 3 times, simply to give us some rows for the demo.

Insert AriTestTbl(AvatarBinary)
SELECT col
FROM OPENROWSET(
BULK N'C:\Ariely_BASE46_VARCHAR\avatar.jpg', SINGLE_BLOB
) Tbl(col)
GO 3
 
SELECT FROM AriTestTbl
GO

  

Convert VARBINARY to Base64 String

In this section I will show how we can use XML built-in options and JSON built-in (from SQL Server 2016) functions in order to convert VARBINARY into Base64. Using variables is a bit different from working with tables. I will show both cases.

Converting Variables from VARBINARY to Base64

Option 1: Convert binary to Base64 using JSON

* You can get the binary value of your image using simple select from our table (we already inserted the image into the table) or you can simply use select query from OPENROWSET (same way we imported the image to the table). In this demo I am using my personal avatar binary value.

The basic logic of this action based on:

  1. We select the value as table
  2. We convert the data in the table into JSON using the built-in hint "for json auto". This operating will automatically convert any binary data type in the table into BASE64 String.
  3. Next, Using the function OPENJSON, we extract the BASE64 String from the JSON
declare @B VARBINARY(max) = 0xFFD8FFE000104A46494600010101004800480000FFDB0043000D090A0B0A080D0B0A0B0E0E0D0F13201513121213271C1E17202E2931302E292D2C333A4A3E333646372C2D405741464C4E525352323E5A615A50604A51524FFFDB0043010E0E0E131113261515264F352D354F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4FFFC000110800DC00CC03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00F4EA28A2800A28A2800A28A2800A28A2801199514BBB055519249C002B39B5CB01034E1A6785467CC8E076423D43018A35CD56C74BD2EEA7BF01E28E3CBC5807783C05C1E0E7A62B92B2D3BC43E2D97FB4754B9934CB10435B5A08C364762C0F53D0E48EF918A00D1FF8583A64B284B2D3F54BBC8E1A2B7F97DBA914DFF84AB5E67017C2ED1A9C10D35CF9639E3072BD7E95D4436F6F689FBB48E3E1433E002D818193DE927BC86DCFEF5650719F96267E3D7E5068039D8FC49AD9728DE1E8CB6D63B23BD52E31D46D2013F8719C7349A7F8DE396548353D26FAC67700A6537A383E8473EBDBB56F5EDBC176504F67E7A81C303864CFE4476AB3846E593073E9CF5A00C8D2BC57A5EA522C0CD25A5D97F2FECD728637DDE833D6B5E6B98A078525621A793CB8F8272DB4B63DB853591ACF87B4CBE02EAE37C335BFCEB7119F9D31CF70722B234AD1B524BFF3A0D69350D3836F8E491F79570A460750064E703A6D5F71401D902092011C75F6A5AE565BCD634DD62EA4686392C150C923850A6593800719200503248EA0F62056FD85E25CC2AAD2C2D72883CE8E370763771D4F7CD005BA28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800AABA96A369A5D8C9797D32C504632CC7F90F53ED52DD5CC1676D25C5D4A914318CB3B9C002BCE13517F1BEB91CB38961D22D1F30408BBDE7939C13C15E9EBC0CF5EA680352FCC7AF5DE9FAA6AEB25B68B11678A3994C5233F014B618E41E48E9C0AEBAD9926B60B6F13C3101B402BB0803D07A5538B4869EEBED7AA4ED738DAD0DBB2811C047A01D4F27939AD6E838A00805AA128D28F31D1B70660323D8549B31F74E307381FD69C33CE48F6E296801AC81A329C804638383F98E948B1850A32C76F4CB1FD7D69F4500262A09ECE399485678988C0646C6DE73C76AB14500672FDAEDB11CFFBE43C2CAA3183C01B941E9C93C71C5620F0E5A047B9F0BDE4361752B07692163229C760338C7AE41AEB2B3AE6D4DA1FB5E9F12A98F7BBC11A28F3B38CFF00C0B81CD0073F67E20D5B49D5D348F10C4B386CB2DFC6A54383F754205E5BE9EFE95D7A3AC881958107A1159B2C767E22D21A266CABE39046F89C7D09C303E9F9D73D6BA8EADE18D412D75D78E4D22422386E5149F2D89E37B13903DCE7B74A00ED68A45218020820F42296800A28A2800A28A2800A28A2800A28A2800A28A8AEA75B6B596E1C12B12339C7A019A00F3DF88535DEAFAED8F876D5D560E269987CF8F7651C8007F3AEEF4AB05D36C21B48E59244890282E07E7C0AE27C0716A3ABEA777AEDE08E2B6B97DFB4261A42385191D5460FD4FAF5AF42A004046480471D7DA969314A738E3AD00274A5A4078E6940C50014D662A320679A752679C60FD680019E738EBC62968A2800A28CD14014D6D9E0B895A17558E5C36DC7461FD0802AAEBD6E6F747BCB5B8921821923E6672081EB90D803EBF8D69CBBF6650804104E7A633CFE94C6059BCA050281961DF1DB8FCFF2A00C3F03DD4D36822D2EAE62B9B8B17303CB1481D5C603290475F9580FC2BA2AE274488E8DF11351B04B28ADED750844F094E012B8071CE3A9638C7E95DB5001451450014514500145145001451450015CF78F6EFEC7E0DD464C292E823019723E6207F5AE86B8FF0089D2A47E1A863941F2A6BC8924C64FCBCB1E075FBB401A1E0AB56834249644915AE1BCC1E67DFDB80141FA0000F6C57419A82C777D8A02E54B18D492A30338EDED53D0014833CE4FD2968A004C75CF39A307239E3D28560EA194E41190696800A28A01C8CD0014D0E0B15E723DA9D45001451450036445923647019581041EE2ABC1162D562901631E40E31C02403F5C558670B8CF52700500F63D4500729AC5E2A7893C397E92BFD9A666853F759C97000C9EA3FA63EB5D6D727AE46E9E2EF0E1B66318334EAC541C11B412A71EB8279FE95D65001451450014514500145145001451450015C97C4AB2FB6F872153208D12F222EC4740494FFD9B3F8575B5C97C47C3E8D616F23110DC6A30C72E0E32A7713CFE00FE1401D3D9B44F6913C19F299014C8C707A54D595E1E7DF693AA80B1C3712411A839C2A1D9CFB9DA4FE35AB400503DE8A2800A28A2800A074A28A0028A28A0028A29A5D44823C8DC41207B7F93400CB840F18CE72ACAC319EA0FB76A70208CE3834B220923647190C3069230003CE4E79E73401C9F8937378A74031248B34576577938428C9961EB9C038F5C115D7D70DA919750F8A1A6DA82043671B5C12ADFECE31F5CE3F035DCD001451450014514500145145001451450015C6F89AE6CFC45A6CF6B179B10B2BD455B991710F98339F981C81D5724632457655C5783A19ADE5F100BE923362F7F28F29C670C5B04E7D08C0FC280353C10F2CBA0BCF2907CEBBB87518C100CADD7DF39ADE8E4120254300091F3291D3EBDAB3BC361C68168F22146954CC558608DEC5B91D8F35612EEDFC997EC252768C162B1B8C67D0B7406802ACDAFD9997CAB3923B920E2568DC32C43D5B193FA52CF73A84CFBB4C6B4689A3CAACCAE189CF241E8463FCF39A6CDA8C96B0844166F7223123C3F6958D6253DF38271EF8E7DAA08F51D566F2DDB4759ADA53912D9DFAB60763CED047D0D006ADA4D33C718B9B768A5312BBF20AAB1EAB9CF51F9559AC6BDBA09A25E4A2EE5CDB2173205DB22639F99703B7B723B7AEBA9DCA086CFF5A008DE50653023624C027183B41CE0FE62A0D406A1E5A369AD0F98AC3724C48523F004E6ACCBB563662A58819C2F538E6A9CD756D7063B679A4495C0778A3C9751C70DB73B473ED4011FDBEF22602E2CE65DDB88D91F9A001D0654FA67B77F6E6D5A5CCB3644F6ED11EAA79C30F5E9C75E879EBE993877AFAB1848B0D2B4C86D631957BB0EC703D515723F3A34EBF37702CB35E5A42F2318D24B4BA6DACE07204722E075FAF4A00E94BA860A58027A0CF5A539E2B3ECAE2F10983518833872A93C406D906339DB9254F5047B5680A003B55685C7EF94657CB908E7B700FE3D6A592458812E739E83B9FA5655D98B53B6778A0768E41B12556C330E3E75C7F0F279EFDB820D0073F0C13E89ABEAFE206D2A79C971101BB05204C02E0FF116C6718EDC919AEDE391258D648D832380CA47420D625E69D0EA13268E2229A6DBC7BA655C81231CED5F7C7DE3EFB6AD78797C9D296D32C7EC6EF6E0B752AA485FFC771401A7451450014514500145145001451450015CA6AF62D6DAB6A1E534E5759B5F2C22B850B2A8DA5B27A7CA47404F04E0F6EAEB2FC49673DE68B38B4665BA8879B015E0EE1D81ED9195FC68033F46865B8B4B5D2EF9A622CE229346F0B2ACD8F9546E38DEA075F53835D1155D9B70318C631C62B3F4FB88752FB36A703921A1DA5431DBF3056E98E4FBFB9F5AD2A00A7258DACD05C41E584F3C7EF08FBC7B03FA7159BE1FF0F3E86595756BCB880E4AC12636A9F6E33FAD6DCB14532ED9A3491739C3804669B1A47192238D50F7C2E07F9E68031B5E8A0D3B4F7BF8E4108898799E665D5A36203AEDEE31C803BFA64D4F671EAED10617B66232B9446B46DCB9E46EF9C738ED8154BC57A65EEA92E9F6F6FCDA497286F016FE043BB8F4CF3923938515D1E31D28039FBEBED42D90437F3DA5B3CD288D5E2272F1F059973D180DDC1E3A1C9E876E3B78615511204099C2AF039EF8EE6B2FC4D6B7D2E9224D2C799796D2ACD1A363F7983CAFE233FCAAFD8891EC237984A92C9FBC2B2E3721273B4E38E338FC2801B751CD36D682491370642015C0C8EA430EC7D2AB43A769FA7581B34B6170B2CC6468F6292CC5B39238000E3E800AD4604E00C67E942AAAE7000C9C9C0C50043359C13DB18258C142A17D08C74C1EA083C83D8D63AEA37F36912C163697B2DD461EDD6E1FC9C19172BBCFCFD370F4FC2BA0A820B786CEDCA44A5537339E4924B12C4FE64D0067695A3456B09798C925E4CB9B89647123364729B88FB80F4031D3EB576E96686112DB056F2636FDD90497E3800F6E9EF56514AE72C5B249E7B7B52B6769C004E38CD0050B5BD84696B7EE02A4B199D883DB19CF3ED8A5D12D64B4D3944E4F9F3334D2E7B3B1C91F8671F8550BB78EEB52B0B5B458DE109B9C0C6154B020E3D08461F8D6F5001451450014514500145145001451450014514500737A240969E21D534E44716E8D1CF1060480760079F41C607B5747DEB3E368575FB8567412B4119553D48CBFFF00135A34000E7B514514015619A493509D028F2235501BD5F9DC3F01B7F3AB55891EBA8BE279344B980DBB18C490484FCB3FAE3DFAF1EC6B56F2EEDEC6D5EE6EE558A18C6599BB500430CF72FA9DD42D06DB78913CB90F1BD8E4B7E5C7E756CFCCBC3639ED58E35D90EF94E91A82DA264FDA191578F5D8583E3FE039F6A7E85A8FF6AFDAEF20666B26942DBB3295DE028DCC33DB39C7D0D006B0CE39E4D30AE410095247DE029F9E714500319589001E0E727BD2A2ED8D54B336D18CB753F5A7514005364758E27773855049FA53AAA6A73182C257040E8B93EE40FEB401058DAC62F7ED11C2B1C690AC6833965381907D30028FCEB4AB3B48F9A4BF915B746F747673D30AAA7FF1E06B46800A28A2800A28A2800A28A2800A28A2800A28A280285F4005E59DE003744FB18FFB2C31EBEBB7D6AFD52D6378D26E5E2243C68645C0C9257E603F4ABA0E46450014668AAACD2B3B867DA0AF1B003B4E7AFAE7A76C7068032FC45629AA5B4282789313AAAB795B9810C3382391C8ED8E9D78AD410AA2AC722F9CAA991BC9639031C039F5EB9EF50ADBCE2632958D4AA7CBB4FCBB9989248F6E39EF93534D1C13BECF3144D957FBD920020F4F4C81ED400C3E6DCB459D8B032E5E29146EE467047620EDFD7DA9F67749728CCA851436D4C8C6E1B41EFF005E9D883485E29E449E0F2A7F2C942570C5738CE0F6F522A2820B882F582844B2DAC046BC92E4EEDD9EC39618FA5006851549310C114114E559A5017CC3B8E33B8AF5FEE861F855C0CAC0104107A628003C724D2839A4C67A80452F7A002B1352B3FEDCBB9AC259A48ED205466F29C066933B97F2001F7C8F4ADB354F4E562B7133EECCD3B3618630061063DB0A0FE3400FD3EC60D3AC62B4B6044518C0DC724E4E4927D726ACD145001451450014514500145145001451450014515CA3EBB2F88354BAD1F471716EB6CD893515C15461FC20746C9E39F73DA801FAB78BADAD6F12D7ECE64B669D609E776DA13712385FBCDD0F3D3DCD74EABB542E49C0C64D62EAFA35A5DE9771E7A24B3B43B4CCFF00292463E6C8E9C8CF1D2AFE8D766FB46B2BB6C6E9A0476C74C903340135DB4CB6D21B650D36D3B01E99ED9F6A6C6822597C83E63E4E559FF8BAF27B751F862A2D62F974ED2E7BC7CFEE9720019DCD9000FC4903F1A4D2DA66B612DD7C92CD993CBF3564C038EE00E9C0E323DCD0065EDF17CAD2381A3C2AE70B148649362FD46324D352DFC60B26F371A1138DBFEA25E9FF007D7E95D2600E698654504B3A8006724E38A00C992CB5F9400755B08B9E4C560C1B1DC026538FCAA292CFC4F0DC2BDAEA9637117468AE2D8AFE3B94F5EB5B9BB3F77919C52E72015C1CD005261702CD965F222BA93EE98FE61BF1C119C67A53E087CB903421511C9674DA7249C927AF1938F5EFEB50EB36767A8DA082F4B81BD5D191B6B2B8390C0FA8EBF9D41A54D78978F6B72AD34263F32DEEC10DBD7382AC477C9C8F507D41A00D8A29339E2968003D0E2B99BDF1A691A56B526937693C2620BFBD11E631919EDC8FCABA6AC1D3B4F8E4D635B9E50C5A5955304630BE528C8FAE68035AC6FAD350B7171637315C44780F1B0233E9F5AB15CF1D1EC7428EEF51D3B4ADF7590CB1DBFC858602E06D0063B91CF7351E87ADEA322476F7F6924B76C7732C71ECF2E3E9924E1490739C63BE01EE01D2D1481D599955812BF7803D296800A28A2800A28A2800A28AADA8DD9B1B196E56096E1907CB144B9673D80A00CBF106BD6563343A64B15C5C5C5E8D822B7FBC149C1624723AF6E78A5D1ED74DD37FD07405802A49FE92BBB2C9D464F727231CD616816F7B1C579AEDD5B4ADAEDEA38B786E011B40190B8EC381E95D558DBC1A6DB2F9A6049E76DD348AA13CD948C93F8F3400E17303C9776AAA1CDB22EF8F1927209039EBC0A934DB98EF34EB7B986331C72C619508FBA31D2B1E691ECB56B9BE96271F6BB38D563420BF98188083DFE7EBD3F2AD3D16D64B3D1ED2DA650B24712AB0CE707BF3DE802D5C27990B284572470ADD09ED9AA06EA2B29365CCEAACCBE6B6E72C172403C9E8BE9F8F4C5699E958B25BAC3A84C74F86D8DF3B89DDA5E5997182A0E7233D01C003D3D4034FCC90C3B805321C10B9FF0038FD7F1ACD44D4EF2F1B74E2D6DC02182C39763965C02D90578CE719391D29D35F416F6325F90EF141BD9950B2B111E43707AFCC3BE0720935751A49234552B1B05FDE0560C5588CFA73D475F5A00A16DA7EAA2EC35EEA626B521498962D84386DD9041E9C018E78E3EB6D4186D5441B622B26D2A559872D8C0F41D31D87A62A781CADAC3E6B61CA2E779C1271CFE3D6A86A3712ACD6F15BABBF9CE5091187893E5C80FF0036476208FA71914015F519244172D35AC934263E24511945033966F9948239E878E0F0722AF584503C9F6AB7D8D1B4616290316DCBDCE4F6381D09CF5EF48D2CAB7F2AF95B952153F2F604F3D39F5C01D769181C12B6CF28D45D0EF96274DCAED114F2F18E338E739CF5EDD2803428A28A002AB5ACD1CB25C2C7F7A3976BFD700FF222AC9AA1358159E4BBB0710DCC9832679497030370FEA39A00B72990347E5A2B02D87CB63031D471C9E9C5635EFF006843A85C4F15A208F66125077861DC328F981CFA71C0A2EEF273BC4825D36E5170B3390D03939C0CE7047E008F6AD4B3BB8AF564F29B3E5398A552305586323F223EB91401CC2DC5BE83A4D9DC693656EC9700AC9732B367793C066C648C93D71803F0AEA2CAED2EE2240D9221DB246482636F438ACFBE592DA5B7B486DE3360D1BA98D602D86038071C60E7B8ED54C4F7161241A84C84C7BFC8BA9149DBE5F2164230070DD48E80927D803A4A28A2800A28AC3D4BC53A6D8DE2D844CF797EE7096B6C37367DCF45FC4F4A00DCAC0D475782E7521A5586AE6DAF546E3B6DFCD5C03CEE24607423A8EF4EDDE219A78997ECB14529DCEB9C88900E80E32CE49EBF7401C67A9A375657FA4787B519D0FDBB51BB0B19541B429384555FF6416EFF00A5006A4B6B7377AAD8ACCCDE5588F35A55F97CE90A950001DB05891EE2A4BF06E6F23B3B8B1692D197779E0FDC90118E3A8FAD36CA1974AD0E0B089C5C5DDBDB8017772D8E09C13D335A51BACB12B8070C3382307F2A00C6D4BE5F14E8A64CEC659D50E0F2FB7383FF00010DFAD6B5BCC27F30AFF04853A7A554D5AD649AC765B63CE85D6788B9CFCCAC1803DF07057E84D4DA5DC5ADE58C77964C1A1B81E6039CF27AFE3DBF0A00B4C70A49C9C73C75ACFBD6319013E49252C2295CE446C54FCD827A718FC7DCD681CF6C535E349576C8A18673823233401970C76A6D62B7865494469B599B0CD2E49182C7B96525BBE454F6845ACFF6572EF34CD2CCCDB4E301871CFA06503E87D29906911DB28861118B7F33CC31B2E7277061F9119CFAF6AB096B26F5692690A08C2F964E79CE739EA49FE9C77A0082DAEEDEF16645DDB8EE07193C601041E9C87E0FF8604082E6DA183FB4246918AB29646C283950A31D4E71BB776F9BB558B8D292E12E15E593371B431E3E5418F9471D3AF5FEF1AB0D6D232BABCEC4333630070A7B739CE28033FED16F24CB1C88E1A7559222EDB64EF9518F9815E723FDAC77C568594223591F0A3CD72E30854E0FA83DFF009F5C735324488495500B1C93DC9A7D001484E31C50083D0E696800A6C6495C904649E0D2B8CA119233C6450A30A07A500437BF66FB1CC6F4466D950B49E60CAED1C927F2AE6B44BB6D43CDD452596DAC228D2578957EF385E7E6EA7E40B91F4FA53FC432C9AEDE9F0ED848446B87BF9979083AAA7D49193EC3DEBA0B0B48ECAD560886141240F4C9CE2802082FCEA16105C69C8E63B956D92C8B811F0705949048CF61FA5655B5DEA91EB10E8B7D141756ED03196E077CB36148FF00740CF5AD9812F63D42E0CF2A496AF8308030D1F1C83C723BE73589AAC50DA789D2E9EFDEC22B8B52F2B8750AE627420107BED623E9F4A0066ADE271E18D620B4D4E3274EB85FDCDC2E4B478C0218771D0E7AF3DEBA682786E6159ADA5496271957460CAC3D88AC4F12EA32DA5BE9B7361F6590CF791C024917780AF919520FD2ABDE786F527B82FA66B7FD9D0B72D0C10614B776C6EEA68026BF3AADFC9B6D56DE5B75C878D2F0C63392305954B1E9D06DC74F9AAA69D616BA65C5CD8F8674D486E38FB45D3B1648CFF74331C93EC06077AE99238A1DCEB1247C724003229CB6F12C61150040DBB03D739CFE7CD0065EA3A23EA71C51DCEA378B0A20578E17D9E69EE588EB9F4E95522D125B4D4ECA3B45863D311DA4688AEE70E00DA4B3649C723A8E0FD2BA3A64BE6796C222A24DA76EE1C67DFDA80284F6934B7F0DE32ED92D4BF96636FF00591B0E5581FC0FD5455A0CCD32F90E8630489467241FF1A82E2EE77D2E79F4F4492E620C046DFDE5EABC73FE4546EF771EA4C6E5604D3C425CCC24C3AB291D7A718FE5D6802A457979A869CE92235A4F7B2C90C4A465A245CA9720FD091DB2CB585E16FB7F87AFEFB4DB84FF00896DB4980C7928A7EEC8319E08C6EE98EBDCD685D4B7F73AAAC977716A9A35C5BAF95346C406048DDF30C152C0E4738C01D48AE9D551790B8EDD2801E8CAE81D1832B0C820E41146D19071C8EF592D6D75A4B6FD32213D9E72D6990AC9FF005C8F4F5F94E07A11D2ACD86AF657EDB2094ACC065A1914A48A3DD4F34017867BD145140051451400514514007D28A2A8DFEAD6B62446E5E59DBEEC10AEF91BF01D07B9C0F7A00BAC70339031EB58373AACFAB4B2D8787A64063204F7B80C9167A84ECCFF00A0FAF1519B2D4F5F7DDAA16B2D34F22CA36C4927B4ADE879F947AF5AD3BBB8B3D074C0C906C894848E185065998E02A8F524D004767A5C1A65B4369651FCAF2032CAD82CFD492C7B9278FC6B481192B9E4726B9ED47C4C9A76B7A76957B04B14B7863DB2AA8742C49057391D0ED19C77E95B6C5D2E8BC9344B06C1853C36EE72739C631DB14009797096F6E6761B9631BB3EDFE3CD64F8A2EADECF478F5B6B7174B667CC4518F983A94EA7A0F981FC2B46CDED96C88D3409914B6D01F219B393F31F73D6B33507BEBFF056A02FADD2DAE5ADE60CB9CA8C67A67DA802B6A3716BAEF8223BF9ED6E22B60639CC709F982AB02718C64707F9FA56F4574A231E4D95CEC2011FBB0BFA120D60E8D752C3F0DA2B8B5DA935BD996538DE32A09CE0E3AE33F8F7AE9A20ED0A177058A8C90B804FD0F4A007140C855BE60460E7BD389C0E68AA97E8B2DACF038CA3A6D6C1C70C706801D6B7505CCB71E43AB989FCB7C1E8700E3FF1EA99255791D14A9287070791C03CFE7515959DB594021B485228D78C28F4E39F5E9445656D0DD4F73144AB34F8F31C756C0C500636B3A65F432CD79A25D79125D1559E261C3F1B4303D55B1DC71ED58FE2056D365D17C416B6AF1EF9317884B3B6D914120E7D002338F4FA57692431CA57CC456DAC19723A1EB9AE5B468C789FC1222D589725C9DE9856054EE047A7A7D2802E476F1DCFF68E877326524FF4BB47DDC8466DD91FEE499FC0AD49A2EA6C91CD63A95C06BEB42B1C8C62D9E613C861D883EDE9EF557C3F66B6DA25AB99A699EDE7DB13CAC0945660A54100718EC7DBD056ACDA1E9D3EB49ABCD6EB25DA4623566E4000920E3D79EB4012D86A515DCD716C3227B693CB910E78F43F420834ED434CB2D4D156EE0576439471C3A1F50C39159F2B2C7E2C82348D17ED16ED248E061894200C9EFC1EF9F6C56DA8C00339A00CCFECFD46D936D8EAACC07DD4BC8C4A00F4DC0AB1FC4934FF003F558D8092C6DE55CF2D15C107FEF965FEB5A345006749A95C444EFD22F586783198DB3FF8F53A3D5237505ED6F6324676B5B3923F20455FA28033CEAF060EDB6BF661D00B39467F12A05363D42F6E33E46933C7E8D7322C63F42C7F4AD2A280284D677774C44D7CD0C27F82DD76B1F62E727F2C1A9ACF4FB4B15616B02465CEE7603E673EA4F527EB566909C5002D52D4A3964B6924B7489EE2152F0798370F3307191534EEC92C414FFAC254FB7CA4E47E5529185C64FD680399D6120D5B45926B858A2D5B4A5170300930CAA3771919284AF5E87EA2B475136C26D3A5BE51E702705412178CB73D8719E7B0AC8F1EA883438E6886D965B88E391C75657C2B03EC40008E95BDA8BAD958471C30C5E517487CB2BF285242918FA5004D6F756F2C519B4C3C6E859360C0C66980215BAB79A788E4125571955239241AC3D575EBAB2D6574DB78E05859A340769DCA1BAE39C7E95A506976965F675D8D7124A1A179AE18C9232329620B1EA32A38A00A1E1A6D32E7C35756FE1EBE7923CCA1091F3425B240DADC0033C0C63F5AD1D1FF00B50E956C2FD604BA540255DC5B0D8EE73D6B97F86EF0B4F770A59DB446140A244421D864F539E7AD5FF13E8B677BAA2CD2A2EF31807F748D9E4FF7949A00FFD9
select col
from openjson(
    (
        select col
        from (SELECT @B as col) T
        for json auto
    )
with(col varchar(max))
Go

Option 2: Convert binary to Base64 using XML XQuery

XQuery (XML Query) is a query language designed to query XML data. Transact-SQL supports a subset of the XQuery language, which can be used for querying xml data type. For more information you can check the links

declare @B varbinary(max) = 0xFFD8FFE000104A46494600010101004800480000FFDB0043000D090A0B0A080D0B0A0B0E0E0D0F13201513121213271C1E17202E2931302E292D2C333A4A3E333646372C2D405741464C4E525352323E5A615A50604A51524FFFDB0043010E0E0E131113261515264F352D354F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4FFFC000110800DC00CC03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00F4EA28A2800A28A2800A28A2800A28A2801199514BBB055519249C002B39B5CB01034E1A6785467CC8E076423D43018A35CD56C74BD2EEA7BF01E28E3CBC5807783C05C1E0E7A62B92B2D3BC43E2D97FB4754B9934CB10435B5A08C364762C0F53D0E48EF918A00D1FF8583A64B284B2D3F54BBC8E1A2B7F97DBA914DFF84AB5E67017C2ED1A9C10D35CF9639E3072BD7E95D4436F6F689FBB48E3E1433E002D818193DE927BC86DCFEF5650719F96267E3D7E5068039D8FC49AD9728DE1E8CB6D63B23BD52E31D46D2013F8719C7349A7F8DE396548353D26FAC67700A6537A383E8473EBDBB56F5EDBC176504F67E7A81C303864CFE4476AB3846E593073E9CF5A00C8D2BC57A5EA522C0CD25A5D97F2FECD728637DDE833D6B5E6B98A078525621A793CB8F8272DB4B63DB853591ACF87B4CBE02EAE37C335BFCEB7119F9D31CF70722B234AD1B524BFF3A0D69350D3836F8E491F79570A460750064E703A6D5F71401D902092011C75F6A5AE565BCD634DD62EA4686392C150C923850A6593800719200503248EA0F62056FD85E25CC2AAD2C2D72883CE8E370763771D4F7CD005BA28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800AABA96A369A5D8C9797D32C504632CC7F90F53ED52DD5CC1676D25C5D4A914318CB3B9C002BCE13517F1BEB91CB38961D22D1F30408BBDE7939C13C15E9EBC0CF5EA680352FCC7AF5DE9FAA6AEB25B68B11678A3994C5233F014B618E41E48E9C0AEBAD9926B60B6F13C3101B402BB0803D07A5538B4869EEBED7AA4ED738DAD0DBB2811C047A01D4F27939AD6E838A00805AA128D28F31D1B70660323D8549B31F74E307381FD69C33CE48F6E296801AC81A329C804638383F98E948B1850A32C76F4CB1FD7D69F4500262A09ECE399485678988C0646C6DE73C76AB14500672FDAEDB11CFFBE43C2CAA3183C01B941E9C93C71C5620F0E5A047B9F0BDE4361752B07692163229C760338C7AE41AEB2B3AE6D4DA1FB5E9F12A98F7BBC11A28F3B38CFF00C0B81CD0073F67E20D5B49D5D348F10C4B386CB2DFC6A54383F754205E5BE9EFE95D7A3AC881958107A1159B2C767E22D21A266CABE39046F89C7D09C303E9F9D73D6BA8EADE18D412D75D78E4D22422386E5149F2D89E37B13903DCE7B74A00ED68A45218020820F42296800A28A2800A28A2800A28A2800A28A2800A28A8AEA75B6B596E1C12B12339C7A019A00F3DF88535DEAFAED8F876D5D560E269987CF8F7651C8007F3AEEF4AB05D36C21B48E59244890282E07E7C0AE27C0716A3ABEA777AEDE08E2B6B97DFB4261A42385191D5460FD4FAF5AF42A004046480471D7DA969314A738E3AD00274A5A4078E6940C50014D662A320679A752679C60FD680019E738EBC62968A2800A28CD14014D6D9E0B895A17558E5C36DC7461FD0802AAEBD6E6F747BCB5B8921821923E6672081EB90D803EBF8D69CBBF6650804104E7A633CFE94C6059BCA050281961DF1DB8FCFF2A00C3F03DD4D36822D2EAE62B9B8B17303CB1481D5C603290475F9580FC2BA2AE274488E8DF11351B04B28ADED750844F094E012B8071CE3A9638C7E95DB5001451450014514500145145001451450015CF78F6EFEC7E0DD464C292E823019723E6207F5AE86B8FF0089D2A47E1A863941F2A6BC8924C64FCBCB1E075FBB401A1E0AB56834249644915AE1BCC1E67DFDB80141FA0000F6C57419A82C777D8A02E54B18D492A30338EDED53D0014833CE4FD2968A004C75CF39A307239E3D28560EA194E41190696800A28A01C8CD0014D0E0B15E723DA9D45001451450036445923647019581041EE2ABC1162D562901631E40E31C02403F5C558670B8CF52700500F63D4500729AC5E2A7893C397E92BFD9A666853F759C97000C9EA3FA63EB5D6D727AE46E9E2EF0E1B66318334EAC541C11B412A71EB8279FE95D65001451450014514500145145001451450015C97C4AB2FB6F872153208D12F222EC4740494FFD9B3F8575B5C97C47C3E8D616F23110DC6A30C72E0E32A7713CFE00FE1401D3D9B44F6913C19F299014C8C707A54D595E1E7DF693AA80B1C3712411A839C2A1D9CFB9DA4FE35AB400503DE8A2800A28A2800A074A28A0028A28A0028A29A5D44823C8DC41207B7F93400CB840F18CE72ACAC319EA0FB76A70208CE3834B220923647190C3069230003CE4E79E73401C9F8937378A74031248B34576577938428C9961EB9C038F5C115D7D70DA919750F8A1A6DA82043671B5C12ADFECE31F5CE3F035DCD001451450014514500145145001451450015C6F89AE6CFC45A6CF6B179B10B2BD455B991710F98339F981C81D5724632457655C5783A19ADE5F100BE923362F7F28F29C670C5B04E7D08C0FC280353C10F2CBA0BCF2907CEBBB87518C100CADD7DF39ADE8E4120254300091F3291D3EBDAB3BC361C68168F22146954CC558608DEC5B91D8F35612EEDFC997EC252768C162B1B8C67D0B7406802ACDAFD9997CAB3923B920E2568DC32C43D5B193FA52CF73A84CFBB4C6B4689A3CAACCAE189CF241E8463FCF39A6CDA8C96B0844166F7223123C3F6958D6253DF38271EF8E7DAA08F51D566F2DDB4759ADA53912D9DFAB60763CED047D0D006ADA4D33C718B9B768A5312BBF20AAB1EAB9CF51F9559AC6BDBA09A25E4A2EE5CDB2173205DB22639F99703B7B723B7AEBA9DCA086CFF5A008DE50653023624C027183B41CE0FE62A0D406A1E5A369AD0F98AC3724C48523F004E6ACCBB563662A58819C2F538E6A9CD756D7063B679A4495C0778A3C9751C70DB73B473ED4011FDBEF22602E2CE65DDB88D91F9A001D0654FA67B77F6E6D5A5CCB3644F6ED11EAA79C30F5E9C75E879EBE993877AFAB1848B0D2B4C86D631957BB0EC703D515723F3A34EBF37702CB35E5A42F2318D24B4BA6DACE07204722E075FAF4A00E94BA860A58027A0CF5A539E2B3ECAE2F10983518833872A93C406D906339DB9254F5047B5680A003B55685C7EF94657CB908E7B700FE3D6A592458812E739E83B9FA5655D98B53B6778A0768E41B12556C330E3E75C7F0F279EFDB820D0073F0C13E89ABEAFE206D2A79C971101BB05204C02E0FF116C6718EDC919AEDE391258D648D832380CA47420D625E69D0EA13268E2229A6DBC7BA655C81231CED5F7C7DE3EFB6AD78797C9D296D32C7EC6EF6E0B752AA485FFC771401A7451450014514500145145001451450015CA6AF62D6DAB6A1E534E5759B5F2C22B850B2A8DA5B27A7CA47404F04E0F6EAEB2FC49673DE68B38B4665BA8879B015E0EE1D81ED9195FC68033F46865B8B4B5D2EF9A622CE229346F0B2ACD8F9546E38DEA075F53835D1155D9B70318C631C62B3F4FB88752FB36A703921A1DA5431DBF3056E98E4FBFB9F5AD2A00A7258DACD05C41E584F3C7EF08FBC7B03FA7159BE1FF0F3E86595756BCB880E4AC12636A9F6E33FAD6DCB14532ED9A3491739C3804669B1A47192238D50F7C2E07F9E68031B5E8A0D3B4F7BF8E4108898799E665D5A36203AEDEE31C803BFA64D4F671EAED10617B66232B9446B46DCB9E46EF9C738ED8154BC57A65EEA92E9F6F6FCDA497286F016FE043BB8F4CF3923938515D1E31D28039FBEBED42D90437F3DA5B3CD288D5E2272F1F059973D180DDC1E3A1C9E876E3B78615511204099C2AF039EF8EE6B2FC4D6B7D2E9224D2C799796D2ACD1A363F7983CAFE233FCAAFD8891EC237984A92C9FBC2B2E3721273B4E38E338FC2801B751CD36D682491370642015C0C8EA430EC7D2AB43A769FA7581B34B6170B2CC6468F6292CC5B39238000E3E800AD4604E00C67E942AAAE7000C9C9C0C50043359C13DB18258C142A17D08C74C1EA083C83D8D63AEA37F36912C163697B2DD461EDD6E1FC9C19172BBCFCFD370F4FC2BA0A820B786CEDCA44A5537339E4924B12C4FE64D0067695A3456B09798C925E4CB9B89647123364729B88FB80F4031D3EB576E96686112DB056F2636FDD90497E3800F6E9EF56514AE72C5B249E7B7B52B6769C004E38CD0050B5BD84696B7EE02A4B199D883DB19CF3ED8A5D12D64B4D3944E4F9F3334D2E7B3B1C91F8671F8550BB78EEB52B0B5B458DE109B9C0C6154B020E3D08461F8D6F5001451450014514500145145001451450014514500737A240969E21D534E44716E8D1CF1060480760079F41C607B5747DEB3E368575FB8567412B4119553D48CBFFF00135A34000E7B514514015619A493509D028F2235501BD5F9DC3F01B7F3AB55891EBA8BE279344B980DBB18C490484FCB3FAE3DFAF1EC6B56F2EEDEC6D5EE6EE558A18C6599BB500430CF72FA9DD42D06DB78913CB90F1BD8E4B7E5C7E756CFCCBC3639ED58E35D90EF94E91A82DA264FDA191578F5D8583E3FE039F6A7E85A8FF6AFDAEF20666B26942DBB3295DE028DCC33DB39C7D0D006B0CE39E4D30AE410095247DE029F9E714500319589001E0E727BD2A2ED8D54B336D18CB753F5A7514005364758E27773855049FA53AAA6A73182C257040E8B93EE40FEB401058DAC62F7ED11C2B1C690AC6833965381907D30028FCEB4AB3B48F9A4BF915B746F747673D30AAA7FF1E06B46800A28A2800A28A2800A28A2800A28A2800A28A280285F4005E59DE003744FB18FFB2C31EBEBB7D6AFD52D6378D26E5E2243C68645C0C9257E603F4ABA0E46450014668AAACD2B3B867DA0AF1B003B4E7AFAE7A76C7068032FC45629AA5B4282789313AAAB795B9810C3382391C8ED8E9D78AD410AA2AC722F9CAA991BC9639031C039F5EB9EF50ADBCE2632958D4AA7CBB4FCBB9989248F6E39EF93534D1C13BECF3144D957FBD920020F4F4C81ED400C3E6DCB459D8B032E5E29146EE467047620EDFD7DA9F67749728CCA851436D4C8C6E1B41EFF005E9D883485E29E449E0F2A7F2C942570C5738CE0F6F522A2820B882F582844B2DAC046BC92E4EEDD9EC39618FA5006851549310C114114E559A5017CC3B8E33B8AF5FEE861F855C0CAC0104107A628003C724D2839A4C67A80452F7A002B1352B3FEDCBB9AC259A48ED205466F29C066933B97F2001F7C8F4ADB354F4E562B7133EECCD3B3618630061063DB0A0FE3400FD3EC60D3AC62B4B6044518C0DC724E4E4927D726ACD145001451450014514500145145001451450014515CA3EBB2F88354BAD1F471716EB6CD893515C15461FC20746C9E39F73DA801FAB78BADAD6F12D7ECE64B669D609E776DA13712385FBCDD0F3D3DCD74EABB542E49C0C64D62EAFA35A5DE9771E7A24B3B43B4CCFF00292463E6C8E9C8CF1D2AFE8D766FB46B2BB6C6E9A0476C74C903340135DB4CB6D21B650D36D3B01E99ED9F6A6C6822597C83E63E4E559FF8BAF27B751F862A2D62F974ED2E7BC7CFEE9720019DCD9000FC4903F1A4D2DA66B612DD7C92CD993CBF3564C038EE00E9C0E323DCD0065EDF17CAD2381A3C2AE70B148649362FD46324D352DFC60B26F371A1138DBFEA25E9FF007D7E95D2600E698654504B3A8006724E38A00C992CB5F9400755B08B9E4C560C1B1DC026538FCAA292CFC4F0DC2BDAEA9637117468AE2D8AFE3B94F5EB5B9BB3F77919C52E72015C1CD005261702CD965F222BA93EE98FE61BF1C119C67A53E087CB903421511C9674DA7249C927AF1938F5EFEB50EB36767A8DA082F4B81BD5D191B6B2B8390C0FA8EBF9D41A54D78978F6B72AD34263F32DEEC10DBD7382AC477C9C8F507D41A00D8A29339E2968003D0E2B99BDF1A691A56B526937693C2620BFBD11E631919EDC8FCABA6AC1D3B4F8E4D635B9E50C5A5955304630BE528C8FAE68035AC6FAD350B7171637315C44780F1B0233E9F5AB15CF1D1EC7428EEF51D3B4ADF7590CB1DBFC858602E06D0063B91CF7351E87ADEA322476F7F6924B76C7732C71ECF2E3E9924E1490739C63BE01EE01D2D1481D599955812BF7803D296800A28A2800A28A2800A28AADA8DD9B1B196E56096E1907CB144B9673D80A00CBF106BD6563343A64B15C5C5C5E8D822B7FBC149C1624723AF6E78A5D1ED74DD37FD07405802A49FE92BBB2C9D464F727231CD616816F7B1C579AEDD5B4ADAEDEA38B786E011B40190B8EC381E95D558DBC1A6DB2F9A6049E76DD348AA13CD948C93F8F3400E17303C9776AAA1CDB22EF8F1927209039EBC0A934DB98EF34EB7B986331C72C619508FBA31D2B1E691ECB56B9BE96271F6BB38D563420BF98188083DFE7EBD3F2AD3D16D64B3D1ED2DA650B24712AB0CE707BF3DE802D5C27990B284572470ADD09ED9AA06EA2B29365CCEAACCBE6B6E72C172403C9E8BE9F8F4C5699E958B25BAC3A84C74F86D8DF3B89DDA5E5997182A0E7233D01C003D3D4034FCC90C3B805321C10B9FF0038FD7F1ACD44D4EF2F1B74E2D6DC02182C39763965C02D90578CE719391D29D35F416F6325F90EF141BD9950B2B111E43707AFCC3BE0720935751A49234552B1B05FDE0560C5588CFA73D475F5A00A16DA7EAA2EC35EEA626B521498962D84386DD9041E9C018E78E3EB6D4186D5441B622B26D2A559872D8C0F41D31D87A62A781CADAC3E6B61CA2E779C1271CFE3D6A86A3712ACD6F15BABBF9CE5091187893E5C80FF0036476208FA71914015F519244172D35AC934263E24511945033966F9948239E878E0F0722AF584503C9F6AB7D8D1B4616290316DCBDCE4F6381D09CF5EF48D2CAB7F2AF95B952153F2F604F3D39F5C01D769181C12B6CF28D45D0EF96274DCAED114F2F18E338E739CF5EDD2803428A28A002AB5ACD1CB25C2C7F7A3976BFD700FF222AC9AA1358159E4BBB0710DCC9832679497030370FEA39A00B72990347E5A2B02D87CB63031D471C9E9C5635EFF006843A85C4F15A208F66125077861DC328F981CFA71C0A2EEF273BC4825D36E5170B3390D03939C0CE7047E008F6AD4B3BB8AF564F29B3E5398A552305586323F223EB91401CC2DC5BE83A4D9DC693656EC9700AC9732B367793C066C648C93D71803F0AEA2CAED2EE2240D9221DB246482636F438ACFBE592DA5B7B486DE3360D1BA98D602D86038071C60E7B8ED54C4F7161241A84C84C7BFC8BA9149DBE5F2164230070DD48E80927D803A4A28A2800A28AC3D4BC53A6D8DE2D844CF797EE7096B6C37367DCF45FC4F4A00DCAC0D475782E7521A5586AE6DAF546E3B6DFCD5C03CEE24607423A8EF4EDDE219A78997ECB14529DCEB9C88900E80E32CE49EBF7401C67A9A375657FA4787B519D0FDBB51BB0B19541B429384555FF6416EFF00A5006A4B6B7377AAD8ACCCDE5588F35A55F97CE90A950001DB05891EE2A4BF06E6F23B3B8B1692D197779E0FDC90118E3A8FAD36CA1974AD0E0B089C5C5DDBDB8017772D8E09C13D335A51BACB12B8070C3382307F2A00C6D4BE5F14E8A64CEC659D50E0F2FB7383FF00010DFAD6B5BCC27F30AFF04853A7A554D5AD649AC765B63CE85D6788B9CFCCAC1803DF07057E84D4DA5DC5ADE58C77964C1A1B81E6039CF27AFE3DBF0A00B4C70A49C9C73C75ACFBD6319013E49252C2295CE446C54FCD827A718FC7DCD681CF6C535E349576C8A18673823233401970C76A6D62B7865494469B599B0CD2E49182C7B96525BBE454F6845ACFF6572EF34CD2CCCDB4E301871CFA06503E87D29906911DB28861118B7F33CC31B2E7277061F9119CFAF6AB096B26F5692690A08C2F964E79CE739EA49FE9C77A0082DAEEDEF16645DDB8EE07193C601041E9C87E0FF8604082E6DA183FB4246918AB29646C283950A31D4E71BB776F9BB558B8D292E12E15E593371B431E3E5418F9471D3AF5FEF1AB0D6D232BABCEC4333630070A7B739CE28033FED16F24CB1C88E1A7559222EDB64EF9518F9815E723FDAC77C568594223591F0A3CD72E30854E0FA83DFF009F5C735324488495500B1C93DC9A7D001484E31C50083D0E696800A6C6495C904649E0D2B8CA119233C6450A30A07A500437BF66FB1CC6F4466D950B49E60CAED1C927F2AE6B44BB6D43CDD452596DAC228D2578957EF385E7E6EA7E40B91F4FA53FC432C9AEDE9F0ED848446B87BF9979083AAA7D49193EC3DEBA0B0B48ECAD560886141240F4C9CE2802082FCEA16105C69C8E63B956D92C8B811F0705949048CF61FA5655B5DEA91EB10E8B7D141756ED03196E077CB36148FF00740CF5AD9812F63D42E0CF2A496AF8308030D1F1C83C723BE73589AAC50DA789D2E9EFDEC22B8B52F2B8750AE627420107BED623E9F4A0066ADE271E18D620B4D4E3274EB85FDCDC2E4B478C0218771D0E7AF3DEBA682786E6159ADA5496271957460CAC3D88AC4F12EA32DA5BE9B7361F6590CF791C024917780AF919520FD2ABDE786F527B82FA66B7FD9D0B72D0C10614B776C6EEA68026BF3AADFC9B6D56DE5B75C878D2F0C63392305954B1E9D06DC74F9AAA69D616BA65C5CD8F8674D486E38FB45D3B1648CFF74331C93EC06077AE99238A1DCEB1247C724003229CB6F12C61150040DBB03D739CFE7CD0065EA3A23EA71C51DCEA378B0A20578E17D9E69EE588EB9F4E95522D125B4D4ECA3B45863D311DA4688AEE70E00DA4B3649C723A8E0FD2BA3A64BE6796C222A24DA76EE1C67DFDA80284F6934B7F0DE32ED92D4BF96636FF00591B0E5581FC0FD5455A0CCD32F90E8630489467241FF1A82E2EE77D2E79F4F4492E620C046DFDE5EABC73FE4546EF771EA4C6E5604D3C425CCC24C3AB291D7A718FE5D6802A457979A869CE92235A4F7B2C90C4A465A245CA9720FD091DB2CB585E16FB7F87AFEFB4DB84FF00896DB4980C7928A7EEC8319E08C6EE98EBDCD685D4B7F73AAAC977716A9A35C5BAF95346C406048DDF30C152C0E4738C01D48AE9D551790B8EDD2801E8CAE81D1832B0C820E41146D19071C8EF592D6D75A4B6FD32213D9E72D6990AC9FF005C8F4F5F94E07A11D2ACD86AF657EDB2094ACC065A1914A48A3DD4F34017867BD145140051451400514514007D28A2A8DFEAD6B62446E5E59DBEEC10AEF91BF01D07B9C0F7A00BAC70339031EB58373AACFAB4B2D8787A64063204F7B80C9167A84ECCFF00A0FAF1519B2D4F5F7DDAA16B2D34F22CA36C4927B4ADE879F947AF5AD3BBB8B3D074C0C906C894848E185065998E02A8F524D004767A5C1A65B4369651FCAF2032CAD82CFD492C7B9278FC6B481192B9E4726B9ED47C4C9A76B7A76957B04B14B7863DB2AA8742C49057391D0ED19C77E95B6C5D2E8BC9344B06C1853C36EE72739C631DB14009797096F6E6761B9631BB3EDFE3CD64F8A2EADECF478F5B6B7174B667CC4518F983A94EA7A0F981FC2B46CDED96C88D3409914B6D01F219B393F31F73D6B33507BEBFF056A02FADD2DAE5ADE60CB9CA8C67A67DA802B6A3716BAEF8223BF9ED6E22B60639CC709F982AB02718C64707F9FA56F4574A231E4D95CEC2011FBB0BFA120D60E8D752C3F0DA2B8B5DA935BD996538DE32A09CE0E3AE33F8F7AE9A20ED0A177058A8C90B804FD0F4A007140C855BE60460E7BD389C0E68AA97E8B2DACF038CA3A6D6C1C70C706801D6B7505CCB71E43AB989FCB7C1E8700E3FF1EA99255791D14A9287070791C03CFE7515959DB594021B485228D78C28F4E39F5E9445656D0DD4F73144AB34F8F31C756C0C500636B3A65F432CD79A25D79125D1559E261C3F1B4303D55B1DC71ED58FE2056D365D17C416B6AF1EF9317884B3B6D914120E7D002338F4FA57692431CA57CC456DAC19723A1EB9AE5B468C789FC1222D589725C9DE9856054EE047A7A7D2802E476F1DCFF68E877326524FF4BB47DDC8466DD91FEE499FC0AD49A2EA6C91CD63A95C06BEB42B1C8C62D9E613C861D883EDE9EF557C3F66B6DA25AB99A699EDE7DB13CAC0945660A54100718EC7DBD056ACDA1E9D3EB49ABCD6EB25DA4623566E4000920E3D79EB4012D86A515DCD716C3227B693CB910E78F43F420834ED434CB2D4D156EE0576439471C3A1F50C39159F2B2C7E2C82348D17ED16ED248E061894200C9EFC1EF9F6C56DA8C00339A00CCFECFD46D936D8EAACC07DD4BC8C4A00F4DC0AB1FC4934FF003F558D8092C6DE55CF2D15C107FEF965FEB5A345006749A95C444EFD22F586783198DB3FF8F53A3D5237505ED6F6324676B5B3923F20455FA28033CEAF060EDB6BF661D00B39467F12A05363D42F6E33E46933C7E8D7322C63F42C7F4AD2A280284D677774C44D7CD0C27F82DD76B1F62E727F2C1A9ACF4FB4B15616B02465CEE7603E673EA4F527EB566909C5002D52D4A3964B6924B7489EE2152F0798370F3307191534EEC92C414FFAC254FB7CA4E47E5529185C64FD680399D6120D5B45926B858A2D5B4A5170300930CAA3771919284AF5E87EA2B475136C26D3A5BE51E702705412178CB73D8719E7B0AC8F1EA883438E6886D965B88E391C75657C2B03EC40008E95BDA8BAD958471C30C5E517487CB2BF285242918FA5004D6F756F2C519B4C3C6E859360C0C66980215BAB79A788E4125571955239241AC3D575EBAB2D6574DB78E05859A340769DCA1BAE39C7E95A506976965F675D8D7124A1A179AE18C9232329620B1EA32A38A00A1E1A6D32E7C35756FE1EBE7923CCA1091F3425B240DADC0033C0C63F5AD1D1FF00B50E956C2FD604BA540255DC5B0D8EE73D6B97F86EF0B4F770A59DB446140A244421D864F539E7AD5FF13E8B677BAA2CD2A2EF31807F748D9E4FF7949A00FFD9
select cast('' as xml).value('xs:base64Binary(sql:variable("@B"))''varchar(max)')
GO

Option 3: Convert binary to Base64 using XML and the hint "for xml path"

declare @B varbinary(max) = 0xFFD8FFE000104A46494600010101004800480000FFDB0043000D090A0B0A080D0B0A0B0E0E0D0F13201513121213271C1E17202E2931302E292D2C333A4A3E333646372C2D405741464C4E525352323E5A615A50604A51524FFFDB0043010E0E0E131113261515264F352D354F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4FFFC000110800DC00CC03012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A535455565758595A636465666768696A737475767778797A838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE1E2E3E4E5E6E7E8E9EAF1F2F3F4F5F6F7F8F9FAFFC4001F0100030101010101010101010000000000000102030405060708090A0BFFC400B51100020102040403040705040400010277000102031104052131061241510761711322328108144291A1B1C109233352F0156272D10A162434E125F11718191A262728292A35363738393A434445464748494A535455565758595A636465666768696A737475767778797A82838485868788898A92939495969798999AA2A3A4A5A6A7A8A9AAB2B3B4B5B6B7B8B9BAC2C3C4C5C6C7C8C9CAD2D3D4D5D6D7D8D9DAE2E3E4E5E6E7E8E9EAF2F3F4F5F6F7F8F9FAFFDA000C03010002110311003F00F4EA28A2800A28A2800A28A2800A28A2801199514BBB055519249C002B39B5CB01034E1A6785467CC8E076423D43018A35CD56C74BD2EEA7BF01E28E3CBC5807783C05C1E0E7A62B92B2D3BC43E2D97FB4754B9934CB10435B5A08C364762C0F53D0E48EF918A00D1FF8583A64B284B2D3F54BBC8E1A2B7F97DBA914DFF84AB5E67017C2ED1A9C10D35CF9639E3072BD7E95D4436F6F689FBB48E3E1433E002D818193DE927BC86DCFEF5650719F96267E3D7E5068039D8FC49AD9728DE1E8CB6D63B23BD52E31D46D2013F8719C7349A7F8DE396548353D26FAC67700A6537A383E8473EBDBB56F5EDBC176504F67E7A81C303864CFE4476AB3846E593073E9CF5A00C8D2BC57A5EA522C0CD25A5D97F2FECD728637DDE833D6B5E6B98A078525621A793CB8F8272DB4B63DB853591ACF87B4CBE02EAE37C335BFCEB7119F9D31CF70722B234AD1B524BFF3A0D69350D3836F8E491F79570A460750064E703A6D5F71401D902092011C75F6A5AE565BCD634DD62EA4686392C150C923850A6593800719200503248EA0F62056FD85E25CC2AAD2C2D72883CE8E370763771D4F7CD005BA28A2800A28A2800A28A2800A28A2800A28A2800A28A2800A28A2800AABA96A369A5D8C9797D32C504632CC7F90F53ED52DD5CC1676D25C5D4A914318CB3B9C002BCE13517F1BEB91CB38961D22D1F30408BBDE7939C13C15E9EBC0CF5EA680352FCC7AF5DE9FAA6AEB25B68B11678A3994C5233F014B618E41E48E9C0AEBAD9926B60B6F13C3101B402BB0803D07A5538B4869EEBED7AA4ED738DAD0DBB2811C047A01D4F27939AD6E838A00805AA128D28F31D1B70660323D8549B31F74E307381FD69C33CE48F6E296801AC81A329C804638383F98E948B1850A32C76F4CB1FD7D69F4500262A09ECE399485678988C0646C6DE73C76AB14500672FDAEDB11CFFBE43C2CAA3183C01B941E9C93C71C5620F0E5A047B9F0BDE4361752B07692163229C760338C7AE41AEB2B3AE6D4DA1FB5E9F12A98F7BBC11A28F3B38CFF00C0B81CD0073F67E20D5B49D5D348F10C4B386CB2DFC6A54383F754205E5BE9EFE95D7A3AC881958107A1159B2C767E22D21A266CABE39046F89C7D09C303E9F9D73D6BA8EADE18D412D75D78E4D22422386E5149F2D89E37B13903DCE7B74A00ED68A45218020820F42296800A28A2800A28A2800A28A2800A28A2800A28A8AEA75B6B596E1C12B12339C7A019A00F3DF88535DEAFAED8F876D5D560E269987CF8F7651C8007F3AEEF4AB05D36C21B48E59244890282E07E7C0AE27C0716A3ABEA777AEDE08E2B6B97DFB4261A42385191D5460FD4FAF5AF42A004046480471D7DA969314A738E3AD00274A5A4078E6940C50014D662A320679A752679C60FD680019E738EBC62968A2800A28CD14014D6D9E0B895A17558E5C36DC7461FD0802AAEBD6E6F747BCB5B8921821923E6672081EB90D803EBF8D69CBBF6650804104E7A633CFE94C6059BCA050281961DF1DB8FCFF2A00C3F03DD4D36822D2EAE62B9B8B17303CB1481D5C603290475F9580FC2BA2AE274488E8DF11351B04B28ADED750844F094E012B8071CE3A9638C7E95DB5001451450014514500145145001451450015CF78F6EFEC7E0DD464C292E823019723E6207F5AE86B8FF0089D2A47E1A863941F2A6BC8924C64FCBCB1E075FBB401A1E0AB56834249644915AE1BCC1E67DFDB80141FA0000F6C57419A82C777D8A02E54B18D492A30338EDED53D0014833CE4FD2968A004C75CF39A307239E3D28560EA194E41190696800A28A01C8CD0014D0E0B15E723DA9D45001451450036445923647019581041EE2ABC1162D562901631E40E31C02403F5C558670B8CF52700500F63D4500729AC5E2A7893C397E92BFD9A666853F759C97000C9EA3FA63EB5D6D727AE46E9E2EF0E1B66318334EAC541C11B412A71EB8279FE95D65001451450014514500145145001451450015C97C4AB2FB6F872153208D12F222EC4740494FFD9B3F8575B5C97C47C3E8D616F23110DC6A30C72E0E32A7713CFE00FE1401D3D9B44F6913C19F299014C8C707A54D595E1E7DF693AA80B1C3712411A839C2A1D9CFB9DA4FE35AB400503DE8A2800A28A2800A074A28A0028A28A0028A29A5D44823C8DC41207B7F93400CB840F18CE72ACAC319EA0FB76A70208CE3834B220923647190C3069230003CE4E79E73401C9F8937378A74031248B34576577938428C9961EB9C038F5C115D7D70DA919750F8A1A6DA82043671B5C12ADFECE31F5CE3F035DCD001451450014514500145145001451450015C6F89AE6CFC45A6CF6B179B10B2BD455B991710F98339F981C81D5724632457655C5783A19ADE5F100BE923362F7F28F29C670C5B04E7D08C0FC280353C10F2CBA0BCF2907CEBBB87518C100CADD7DF39ADE8E4120254300091F3291D3EBDAB3BC361C68168F22146954CC558608DEC5B91D8F35612EEDFC997EC252768C162B1B8C67D0B7406802ACDAFD9997CAB3923B920E2568DC32C43D5B193FA52CF73A84CFBB4C6B4689A3CAACCAE189CF241E8463FCF39A6CDA8C96B0844166F7223123C3F6958D6253DF38271EF8E7DAA08F51D566F2DDB4759ADA53912D9DFAB60763CED047D0D006ADA4D33C718B9B768A5312BBF20AAB1EAB9CF51F9559AC6BDBA09A25E4A2EE5CDB2173205DB22639F99703B7B723B7AEBA9DCA086CFF5A008DE50653023624C027183B41CE0FE62A0D406A1E5A369AD0F98AC3724C48523F004E6ACCBB563662A58819C2F538E6A9CD756D7063B679A4495C0778A3C9751C70DB73B473ED4011FDBEF22602E2CE65DDB88D91F9A001D0654FA67B77F6E6D5A5CCB3644F6ED11EAA79C30F5E9C75E879EBE993877AFAB1848B0D2B4C86D631957BB0EC703D515723F3A34EBF37702CB35E5A42F2318D24B4BA6DACE07204722E075FAF4A00E94BA860A58027A0CF5A539E2B3ECAE2F10983518833872A93C406D906339DB9254F5047B5680A003B55685C7EF94657CB908E7B700FE3D6A592458812E739E83B9FA5655D98B53B6778A0768E41B12556C330E3E75C7F0F279EFDB820D0073F0C13E89ABEAFE206D2A79C971101BB05204C02E0FF116C6718EDC919AEDE391258D648D832380CA47420D625E69D0EA13268E2229A6DBC7BA655C81231CED5F7C7DE3EFB6AD78797C9D296D32C7EC6EF6E0B752AA485FFC771401A7451450014514500145145001451450015CA6AF62D6DAB6A1E534E5759B5F2C22B850B2A8DA5B27A7CA47404F04E0F6EAEB2FC49673DE68B38B4665BA8879B015E0EE1D81ED9195FC68033F46865B8B4B5D2EF9A622CE229346F0B2ACD8F9546E38DEA075F53835D1155D9B70318C631C62B3F4FB88752FB36A703921A1DA5431DBF3056E98E4FBFB9F5AD2A00A7258DACD05C41E584F3C7EF08FBC7B03FA7159BE1FF0F3E86595756BCB880E4AC12636A9F6E33FAD6DCB14532ED9A3491739C3804669B1A47192238D50F7C2E07F9E68031B5E8A0D3B4F7BF8E4108898799E665D5A36203AEDEE31C803BFA64D4F671EAED10617B66232B9446B46DCB9E46EF9C738ED8154BC57A65EEA92E9F6F6FCDA497286F016FE043BB8F4CF3923938515D1E31D28039FBEBED42D90437F3DA5B3CD288D5E2272F1F059973D180DDC1E3A1C9E876E3B78615511204099C2AF039EF8EE6B2FC4D6B7D2E9224D2C799796D2ACD1A363F7983CAFE233FCAAFD8891EC237984A92C9FBC2B2E3721273B4E38E338FC2801B751CD36D682491370642015C0C8EA430EC7D2AB43A769FA7581B34B6170B2CC6468F6292CC5B39238000E3E800AD4604E00C67E942AAAE7000C9C9C0C50043359C13DB18258C142A17D08C74C1EA083C83D8D63AEA37F36912C163697B2DD461EDD6E1FC9C19172BBCFCFD370F4FC2BA0A820B786CEDCA44A5537339E4924B12C4FE64D0067695A3456B09798C925E4CB9B89647123364729B88FB80F4031D3EB576E96686112DB056F2636FDD90497E3800F6E9EF56514AE72C5B249E7B7B52B6769C004E38CD0050B5BD84696B7EE02A4B199D883DB19CF3ED8A5D12D64B4D3944E4F9F3334D2E7B3B1C91F8671F8550BB78EEB52B0B5B458DE109B9C0C6154B020E3D08461F8D6F5001451450014514500145145001451450014514500737A240969E21D534E44716E8D1CF1060480760079F41C607B5747DEB3E368575FB8567412B4119553D48CBFFF00135A34000E7B514514015619A493509D028F2235501BD5F9DC3F01B7F3AB55891EBA8BE279344B980DBB18C490484FCB3FAE3DFAF1EC6B56F2EEDEC6D5EE6EE558A18C6599BB500430CF72FA9DD42D06DB78913CB90F1BD8E4B7E5C7E756CFCCBC3639ED58E35D90EF94E91A82DA264FDA191578F5D8583E3FE039F6A7E85A8FF6AFDAEF20666B26942DBB3295DE028DCC33DB39C7D0D006B0CE39E4D30AE410095247DE029F9E714500319589001E0E727BD2A2ED8D54B336D18CB753F5A7514005364758E27773855049FA53AAA6A73182C257040E8B93EE40FEB401058DAC62F7ED11C2B1C690AC6833965381907D30028FCEB4AB3B48F9A4BF915B746F747673D30AAA7FF1E06B46800A28A2800A28A2800A28A2800A28A2800A28A280285F4005E59DE003744FB18FFB2C31EBEBB7D6AFD52D6378D26E5E2243C68645C0C9257E603F4ABA0E46450014668AAACD2B3B867DA0AF1B003B4E7AFAE7A76C7068032FC45629AA5B4282789313AAAB795B9810C3382391C8ED8E9D78AD410AA2AC722F9CAA991BC9639031C039F5EB9EF50ADBCE2632958D4AA7CBB4FCBB9989248F6E39EF93534D1C13BECF3144D957FBD920020F4F4C81ED400C3E6DCB459D8B032E5E29146EE467047620EDFD7DA9F67749728CCA851436D4C8C6E1B41EFF005E9D883485E29E449E0F2A7F2C942570C5738CE0F6F522A2820B882F582844B2DAC046BC92E4EEDD9EC39618FA5006851549310C114114E559A5017CC3B8E33B8AF5FEE861F855C0CAC0104107A628003C724D2839A4C67A80452F7A002B1352B3FEDCBB9AC259A48ED205466F29C066933B97F2001F7C8F4ADB354F4E562B7133EECCD3B3618630061063DB0A0FE3400FD3EC60D3AC62B4B6044518C0DC724E4E4927D726ACD145001451450014514500145145001451450014515CA3EBB2F88354BAD1F471716EB6CD893515C15461FC20746C9E39F73DA801FAB78BADAD6F12D7ECE64B669D609E776DA13712385FBCDD0F3D3DCD74EABB542E49C0C64D62EAFA35A5DE9771E7A24B3B43B4CCFF00292463E6C8E9C8CF1D2AFE8D766FB46B2BB6C6E9A0476C74C903340135DB4CB6D21B650D36D3B01E99ED9F6A6C6822597C83E63E4E559FF8BAF27B751F862A2D62F974ED2E7BC7CFEE9720019DCD9000FC4903F1A4D2DA66B612DD7C92CD993CBF3564C038EE00E9C0E323DCD0065EDF17CAD2381A3C2AE70B148649362FD46324D352DFC60B26F371A1138DBFEA25E9FF007D7E95D2600E698654504B3A8006724E38A00C992CB5F9400755B08B9E4C560C1B1DC026538FCAA292CFC4F0DC2BDAEA9637117468AE2D8AFE3B94F5EB5B9BB3F77919C52E72015C1CD005261702CD965F222BA93EE98FE61BF1C119C67A53E087CB903421511C9674DA7249C927AF1938F5EFEB50EB36767A8DA082F4B81BD5D191B6B2B8390C0FA8EBF9D41A54D78978F6B72AD34263F32DEEC10DBD7382AC477C9C8F507D41A00D8A29339E2968003D0E2B99BDF1A691A56B526937693C2620BFBD11E631919EDC8FCABA6AC1D3B4F8E4D635B9E50C5A5955304630BE528C8FAE68035AC6FAD350B7171637315C44780F1B0233E9F5AB15CF1D1EC7428EEF51D3B4ADF7590CB1DBFC858602E06D0063B91CF7351E87ADEA322476F7F6924B76C7732C71ECF2E3E9924E1490739C63BE01EE01D2D1481D599955812BF7803D296800A28A2800A28A2800A28AADA8DD9B1B196E56096E1907CB144B9673D80A00CBF106BD6563343A64B15C5C5C5E8D822B7FBC149C1624723AF6E78A5D1ED74DD37FD07405802A49FE92BBB2C9D464F727231CD616816F7B1C579AEDD5B4ADAEDEA38B786E011B40190B8EC381E95D558DBC1A6DB2F9A6049E76DD348AA13CD948C93F8F3400E17303C9776AAA1CDB22EF8F1927209039EBC0A934DB98EF34EB7B986331C72C619508FBA31D2B1E691ECB56B9BE96271F6BB38D563420BF98188083DFE7EBD3F2AD3D16D64B3D1ED2DA650B24712AB0CE707BF3DE802D5C27990B284572470ADD09ED9AA06EA2B29365CCEAACCBE6B6E72C172403C9E8BE9F8F4C5699E958B25BAC3A84C74F86D8DF3B89DDA5E5997182A0E7233D01C003D3D4034FCC90C3B805321C10B9FF0038FD7F1ACD44D4EF2F1B74E2D6DC02182C39763965C02D90578CE719391D29D35F416F6325F90EF141BD9950B2B111E43707AFCC3BE0720935751A49234552B1B05FDE0560C5588CFA73D475F5A00A16DA7EAA2EC35EEA626B521498962D84386DD9041E9C018E78E3EB6D4186D5441B622B26D2A559872D8C0F41D31D87A62A781CADAC3E6B61CA2E779C1271CFE3D6A86A3712ACD6F15BABBF9CE5091187893E5C80FF0036476208FA71914015F519244172D35AC934263E24511945033966F9948239E878E0F0722AF584503C9F6AB7D8D1B4616290316DCBDCE4F6381D09CF5EF48D2CAB7F2AF95B952153F2F604F3D39F5C01D769181C12B6CF28D45D0EF96274DCAED114F2F18E338E739CF5EDD2803428A28A002AB5ACD1CB25C2C7F7A3976BFD700FF222AC9AA1358159E4BBB0710DCC9832679497030370FEA39A00B72990347E5A2B02D87CB63031D471C9E9C5635EFF006843A85C4F15A208F66125077861DC328F981CFA71C0A2EEF273BC4825D36E5170B3390D03939C0CE7047E008F6AD4B3BB8AF564F29B3E5398A552305586323F223EB91401CC2DC5BE83A4D9DC693656EC9700AC9732B367793C066C648C93D71803F0AEA2CAED2EE2240D9221DB246482636F438ACFBE592DA5B7B486DE3360D1BA98D602D86038071C60E7B8ED54C4F7161241A84C84C7BFC8BA9149DBE5F2164230070DD48E80927D803A4A28A2800A28AC3D4BC53A6D8DE2D844CF797EE7096B6C37367DCF45FC4F4A00DCAC0D475782E7521A5586AE6DAF546E3B6DFCD5C03CEE24607423A8EF4EDDE219A78997ECB14529DCEB9C88900E80E32CE49EBF7401C67A9A375657FA4787B519D0FDBB51BB0B19541B429384555FF6416EFF00A5006A4B6B7377AAD8ACCCDE5588F35A55F97CE90A950001DB05891EE2A4BF06E6F23B3B8B1692D197779E0FDC90118E3A8FAD36CA1974AD0E0B089C5C5DDBDB8017772D8E09C13D335A51BACB12B8070C3382307F2A00C6D4BE5F14E8A64CEC659D50E0F2FB7383FF00010DFAD6B5BCC27F30AFF04853A7A554D5AD649AC765B63CE85D6788B9CFCCAC1803DF07057E84D4DA5DC5ADE58C77964C1A1B81E6039CF27AFE3DBF0A00B4C70A49C9C73C75ACFBD6319013E49252C2295CE446C54FCD827A718FC7DCD681CF6C535E349576C8A18673823233401970C76A6D62B7865494469B599B0CD2E49182C7B96525BBE454F6845ACFF6572EF34CD2CCCDB4E301871CFA06503E87D29906911DB28861118B7F33CC31B2E7277061F9119CFAF6AB096B26F5692690A08C2F964E79CE739EA49FE9C77A0082DAEEDEF16645DDB8EE07193C601041E9C87E0FF8604082E6DA183FB4246918AB29646C283950A31D4E71BB776F9BB558B8D292E12E15E593371B431E3E5418F9471D3AF5FEF1AB0D6D232BABCEC4333630070A7B739CE28033FED16F24CB1C88E1A7559222EDB64EF9518F9815E723FDAC77C568594223591F0A3CD72E30854E0FA83DFF009F5C735324488495500B1C93DC9A7D001484E31C50083D0E696800A6C6495C904649E0D2B8CA119233C6450A30A07A500437BF66FB1CC6F4466D950B49E60CAED1C927F2AE6B44BB6D43CDD452596DAC228D2578957EF385E7E6EA7E40B91F4FA53FC432C9AEDE9F0ED848446B87BF9979083AAA7D49193EC3DEBA0B0B48ECAD560886141240F4C9CE2802082FCEA16105C69C8E63B956D92C8B811F0705949048CF61FA5655B5DEA91EB10E8B7D141756ED03196E077CB36148FF00740CF5AD9812F63D42E0CF2A496AF8308030D1F1C83C723BE73589AAC50DA789D2E9EFDEC22B8B52F2B8750AE627420107BED623E9F4A0066ADE271E18D620B4D4E3274EB85FDCDC2E4B478C0218771D0E7AF3DEBA682786E6159ADA5496271957460CAC3D88AC4F12EA32DA5BE9B7361F6590CF791C024917780AF919520FD2ABDE786F527B82FA66B7FD9D0B72D0C10614B776C6EEA68026BF3AADFC9B6D56DE5B75C878D2F0C63392305954B1E9D06DC74F9AAA69D616BA65C5CD8F8674D486E38FB45D3B1648CFF74331C93EC06077AE99238A1DCEB1247C724003229CB6F12C61150040DBB03D739CFE7CD0065EA3A23EA71C51DCEA378B0A20578E17D9E69EE588EB9F4E95522D125B4D4ECA3B45863D311DA4688AEE70E00DA4B3649C723A8E0FD2BA3A64BE6796C222A24DA76EE1C67DFDA80284F6934B7F0DE32ED92D4BF96636FF00591B0E5581FC0FD5455A0CCD32F90E8630489467241FF1A82E2EE77D2E79F4F4492E620C046DFDE5EABC73FE4546EF771EA4C6E5604D3C425CCC24C3AB291D7A718FE5D6802A457979A869CE92235A4F7B2C90C4A465A245CA9720FD091DB2CB585E16FB7F87AFEFB4DB84FF00896DB4980C7928A7EEC8319E08C6EE98EBDCD685D4B7F73AAAC977716A9A35C5BAF95346C406048DDF30C152C0E4738C01D48AE9D551790B8EDD2801E8CAE81D1832B0C820E41146D19071C8EF592D6D75A4B6FD32213D9E72D6990AC9FF005C8F4F5F94E07A11D2ACD86AF657EDB2094ACC065A1914A48A3DD4F34017867BD145140051451400514514007D28A2A8DFEAD6B62446E5E59DBEEC10AEF91BF01D07B9C0F7A00BAC70339031EB58373AACFAB4B2D8787A64063204F7B80C9167A84ECCFF00A0FAF1519B2D4F5F7DDAA16B2D34F22CA36C4927B4ADE879F947AF5AD3BBB8B3D074C0C906C894848E185065998E02A8F524D004767A5C1A65B4369651FCAF2032CAD82CFD492C7B9278FC6B481192B9E4726B9ED47C4C9A76B7A76957B04B14B7863DB2AA8742C49057391D0ED19C77E95B6C5D2E8BC9344B06C1853C36EE72739C631DB14009797096F6E6761B9631BB3EDFE3CD64F8A2EADECF478F5B6B7174B667CC4518F983A94EA7A0F981FC2B46CDED96C88D3409914B6D01F219B393F31F73D6B33507BEBFF056A02FADD2DAE5ADE60CB9CA8C67A67DA802B6A3716BAEF8223BF9ED6E22B60639CC709F982AB02718C64707F9FA56F4574A231E4D95CEC2011FBB0BFA120D60E8D752C3F0DA2B8B5DA935BD996538DE32A09CE0E3AE33F8F7AE9A20ED0A177058A8C90B804FD0F4A007140C855BE60460E7BD389C0E68AA97E8B2DACF038CA3A6D6C1C70C706801D6B7505CCB71E43AB989FCB7C1E8700E3FF1EA99255791D14A9287070791C03CFE7515959DB594021B485228D78C28F4E39F5E9445656D0DD4F73144AB34F8F31C756C0C500636B3A65F432CD79A25D79125D1559E261C3F1B4303D55B1DC71ED58FE2056D365D17C416B6AF1EF9317884B3B6D914120E7D002338F4FA57692431CA57CC456DAC19723A1EB9AE5B468C789FC1222D589725C9DE9856054EE047A7A7D2802E476F1DCFF68E877326524FF4BB47DDC8466DD91FEE499FC0AD49A2EA6C91CD63A95C06BEB42B1C8C62D9E613C861D883EDE9EF557C3F66B6DA25AB99A699EDE7DB13CAC0945660A54100718EC7DBD056ACDA1E9D3EB49ABCD6EB25DA4623566E4000920E3D79EB4012D86A515DCD716C3227B693CB910E78F43F420834ED434CB2D4D156EE0576439471C3A1F50C39159F2B2C7E2C82348D17ED16ED248E061894200C9EFC1EF9F6C56DA8C00339A00CCFECFD46D936D8EAACC07DD4BC8C4A00F4DC0AB1FC4934FF003F558D8092C6DE55CF2D15C107FEF965FEB5A345006749A95C444EFD22F586783198DB3FF8F53A3D5237505ED6F6324676B5B3923F20455FA28033CEAF060EDB6BF661D00B39467F12A05363D42F6E33E46933C7E8D7322C63F42C7F4AD2A280284D677774C44D7CD0C27F82DD76B1F62E727F2C1A9ACF4FB4B15616B02465CEE7603E673EA4F527EB566909C5002D52D4A3964B6924B7489EE2152F0798370F3307191534EEC92C414FFAC254FB7CA4E47E5529185C64FD680399D6120D5B45926B858A2D5B4A5170300930CAA3771919284AF5E87EA2B475136C26D3A5BE51E702705412178CB73D8719E7B0AC8F1EA883438E6886D965B88E391C75657C2B03EC40008E95BDA8BAD958471C30C5E517487CB2BF285242918FA5004D6F756F2C519B4C3C6E859360C0C66980215BAB79A788E4125571955239241AC3D575EBAB2D6574DB78E05859A340769DCA1BAE39C7E95A506976965F675D8D7124A1A179AE18C9232329620B1EA32A38A00A1E1A6D32E7C35756FE1EBE7923CCA1091F3425B240DADC0033C0C63F5AD1D1FF00B50E956C2FD604BA540255DC5B0D8EE73D6B97F86EF0B4F770A59DB446140A244421D864F539E7AD5FF13E8B677BAA2CD2A2EF31807F748D9E4FF7949A00FFD9
select from (select @B as '*') Tbl
for xml path('')
GO

 

Converting column values from VARBINARY to Base64

 

Option 1: Convert binary to Base64 using JSON

select Id,AvatarBinary
from openjson(
    (
        select Id,AvatarBinary
        from AriTestTbl
        for json auto
    )
with(Id int, AvatarBinary varchar(max))
GO

 

Option 2: Convert binary to Base64 using XML XQuery

select Id,
    cast('' as xml).value(
        'xs:base64Binary(sql:column("AriTestTbl.AvatarBinary"))''varchar(max)'
    )
from AriTestTbl
GO

 

Option 3: Convert binary to Base64 using XML and the hint "for xml path"

select Id,AvatarBinary,s
from AriTestTbl
cross apply (select AvatarBinary as '*' for xml path('')) T (s)
GO

 

Convert Base64 String to VARBINARY

Basically for each option, which I demonstrated above, there is an equivalent option to convert the data in this direction. For the sake of this blog post, I will focus on one example at this time.

Converting Variables from Base64 to VARBINARY

declare @Base64String varchar(max) =
    '/9j/4AAQSkZJRgABAQEASABIAAD/2wBDAA0JCgsKCA0LCgsODg0PEyAVExISEyccHhcgLikxMC4pLSwzOko+MzZGNywtQFdBRkxOUlNSMj5aYVpQYEpRUk//2wBDAQ4ODhMREyYVFSZPNS01T09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT09PT0//wAARCADcAMwDASIAAhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD06iiigAooooAKKKKACiiigBGZUUu7BVUZJJwAKzm1ywEDThpnhUZ8yOB2Qj1DAYo1zVbHS9Lup78B4o48vFgHeDwFweDnpiuSstO8Q+LZf7R1S5k0yxBDW1oIw2R2LA9T0OSO+RigDR/4WDpksoSy0/VLvI4aK3+X26kU3/hKteZwF8LtGpwQ01z5Y54wcr1+ldRDb29on7tI4+FDPgAtgYGT3pJ7yG3P71ZQcZ+WJn49flBoA52PxJrZco3h6MttY7I71S4x1G0gE/hxnHNJp/jeOWVINT0m+sZ3AKZTejg+hHPr27VvXtvBdlBPZ+eoHDA4ZM/kR2qzhG5ZMHPpz1oAyNK8V6XqUiwM0lpdl/L+zXKGN93oM9a15rmKB4UlYhp5PLj4Jy20tj24U1kaz4e0y+AurjfDNb/OtxGfnTHPcHIrI0rRtSS/86DWk1DTg2+OSR95VwpGB1AGTnA6bV9xQB2QIJIBHHX2pa5WW81jTdYupGhjksFQySOFCmWTgAcZIAUDJI6g9iBW/YXiXMKq0sLXKIPOjjcHY3cdT3zQBbooooAKKKKACiiigAooooAKKKKACiiigAooooAKq6lqNppdjJeX0yxQRjLMf5D1PtUt1cwWdtJcXUqRQxjLO5wAK84TUX8b65HLOJYdItHzBAi73nk5wTwV6evAz16mgDUvzHr13p+qausltosRZ4o5lMUjPwFLYY5B5I6cCuutmSa2C28TwxAbQCuwgD0HpVOLSGnuvteqTtc42tDbsoEcBHoB1PJ5Oa1ug4oAgFqhKNKPMdG3BmAyPYVJsx904wc4H9acM85I9uKWgBrIGjKcgEY4OD+Y6UixhQoyx29Msf19afRQAmKgns45lIVniYjAZGxt5zx2qxRQBnL9rtsRz/vkPCyqMYPAG5QenJPHHFYg8OWgR7nwveQ2F1KwdpIWMinHYDOMeuQa6ys65tTaH7Xp8SqY97vBGijzs4z/AMC4HNAHP2fiDVtJ1dNI8QxLOGyy38alQ4P3VCBeW+nv6V16OsiBlYEHoRWbLHZ+ItIaJmyr45BG+Jx9CcMD6fnXPWuo6t4Y1BLXXXjk0iQiOG5RSfLYnjexOQPc57dKAO1opFIYAggg9CKWgAooooAKKKKACiiigAooooAKKKiup1trWW4cErEjOcegGaAPPfiFNd6vrtj4dtXVYOJpmHz492UcgAfzru9KsF02whtI5ZJEiQKC4H58CuJ8Bxajq+p3eu3gjitrl9+0JhpCOFGR1UYP1Pr1r0KgBARkgEcdfalpMUpzjjrQAnSlpAeOaUDFABTWYqMgZ5p1JnnGD9aAAZ5zjrxiloooAKKM0UAU1tnguJWhdVjlw23HRh/QgCquvW5vdHvLW4khghkj5mcggeuQ2APr+Nacu/ZlCAQQTnpjPP6UxgWbygUCgZYd8duPz/KgDD8D3U02gi0urmK5uLFzA8sUgdXGAykEdflYD8K6KuJ0SI6N8RNRsEsore11CETwlOASuAcc46ljjH6V21ABRRRQAUUUUAFFFFABRRRQAVz3j27+x+DdRkwpLoIwGXI+Ygf1roa4/wCJ0qR+GoY5QfKmvIkkxk/Lyx4HX7tAGh4KtWg0JJZEkVrhvMHmff24AUH6AAD2xXQZqCx3fYoC5UsY1JKjAzjt7VPQAUgzzk/SlooATHXPOaMHI549KFYOoZTkEZBpaACiigHIzQAU0OCxXnI9qdRQAUUUUANkRZI2RwGVgQQe4qvBFi1WKQFjHkDjHAJAP1xVhnC4z1JwBQD2PUUAcprF4qeJPDl+kr/ZpmaFP3WclwAMnqP6Y+tdbXJ65G6eLvDhtmMYM06sVBwRtBKnHrgnn+ldZQAUUUUAFFFFABRRRQAUUUUAFcl8SrL7b4chUyCNEvIi7EdASU/9mz+FdbXJfEfD6NYW8jEQ3Gowxy4OMqdxPP4A/hQB09m0T2kTwZ8pkBTIxwelTVleHn32k6qAscNxJBGoOcKh2c+52k/jWrQAUD3oooAKKKKACgdKKKACiiigAooppdRII8jcQSB7f5NADLhA8YznKsrDGeoPt2pwIIzjg0siCSNkcZDDBpIwADzk555zQByfiTc3inQDEkizRXZXeThCjJlh65wDj1wRXX1w2pGXUPihptqCBDZxtcEq3+zjH1zj8DXc0AFFFFABRRRQAUUUUAFFFFABXG+Jrmz8RabPaxebELK9RVuZFxD5gzn5gcgdVyRjJFdlXFeDoZreXxAL6SM2L38o8pxnDFsE59CMD8KANTwQ8sugvPKQfOu7h1GMEAyt1985rejkEgJUMACR8ykdPr2rO8NhxoFo8iFGlUzFWGCN7FuR2PNWEu7fyZfsJSdowWKxuMZ9C3QGgCrNr9mZfKs5I7kg4laNwyxD1bGT+lLPc6hM+7TGtGiaPKrMrhic8kHoRj/POabNqMlrCEQWb3IjEjw/aVjWJT3zgnHvjn2qCPUdVm8t20dZraU5Etnfq2B2PO0EfQ0AatpNM8cYubdopTErvyCqseq5z1H5VZrGvboJol5KLuXNshcyBdsiY5+ZcDt7cjt666ncoIbP9aAI3lBlMCNiTAJxg7Qc4P5ioNQGoeWjaa0PmKw3JMSFI/AE5qzLtWNmKliBnC9Tjmqc11bXBjtnmkSVwHeKPJdRxw23O0c+1AEf2+8iYC4s5l3biNkfmgAdBlT6Z7d/bm1aXMs2RPbtEeqnnDD16cdeh56+mTh3r6sYSLDStMhtYxlXuw7HA9UVcj86NOvzdwLLNeWkLyMY0ktLptrOByBHIuB1+vSgDpS6hgpYAnoM9aU54rPsri8QmDUYgzhyqTxAbZBjOduSVPUEe1aAoAO1VoXH75RlfLkI57cA/j1qWSRYgS5znoO5+lZV2YtTtneKB2jkGxJVbDMOPnXH8PJ579uCDQBz8ME+iavq/iBtKnnJcRAbsFIEwC4P8RbGcY7ckZrt45EljWSNgyOAykdCDWJeadDqEyaOIimm28e6ZVyBIxztX3x94++2rXh5fJ0pbTLH7G724LdSqkhf/HcUAadFFFABRRRQAUUUUAFFFFABXKavYtbatqHlNOV1m18sIrhQsqjaWyenykdATwTg9urrL8SWc95os4tGZbqIebAV4O4dge2RlfxoAz9GhluLS10u+aYiziKTRvCyrNj5VG443qB19Tg10RVdm3AxjGMcYrP0+4h1L7NqcDkhodpUMdvzBW6Y5Pv7n1rSoApyWNrNBcQeWE88fvCPvHsD+nFZvh/w8+hllXVry4gOSsEmNqn24z+tbcsUUy7Zo0kXOcOARmmxpHGSI41Q98Lgf55oAxteig07T3v45BCImHmeZl1aNiA67e4xyAO/pk1PZx6u0QYXtmIyuURrRty55G75xzjtgVS8V6Ze6pLp9vb82klyhvAW/gQ7uPTPOSOThRXR4x0oA5++vtQtkEN/PaWzzSiNXiJy8fBZlz0YDdweOhyeh247eGFVESBAmcKvA5747msvxNa30ukiTSx5l5bSrNGjY/eYPK/iM/yq/YiR7CN5hKksn7wrLjchJztOOOM4/CgBt1HNNtaCSRNwZCAVwMjqQw7H0qtDp2n6dYGzS2FwssxkaPYpLMWzkjgADj6ACtRgTgDGfpQqqucADJycDFAEM1nBPbGCWMFCoX0Ix0weoIPIPY1jrqN/NpEsFjaXst1GHt1uH8nBkXK7z8/TcPT8K6CoILeGztykSlU3M55JJLEsT+ZNAGdpWjRWsJeYySXky5uJZHEjNkcpuI+4D0Ax0+tXbpZoYRLbBW8mNv3ZBJfjgA9unvVlFK5yxbJJ57e1K2dpwATjjNAFC1vYRpa37gKksZnYg9sZzz7YpdEtZLTTlE5PnzM00uezsckfhnH4VQu3jutSsLW0WN4Qm5wMYVSwIOPQhGH41vUAFFFFABRRRQAUUUUAFFFFABRRRQBzeiQJaeIdU05EcW6NHPEGBIB2AHn0HGB7V0fes+NoV1+4VnQStBGVU9SMv/8AE1o0AA57UUUUAVYZpJNQnQKPIjVQG9X53D8Bt/OrVYkeuovieTRLmA27GMSQSE/LP649+vHsa1by7t7G1e5u5VihjGWZu1AEMM9y+p3ULQbbeJE8uQ8b2OS35cfnVs/MvDY57VjjXZDvlOkagtomT9oZFXj12Fg+P+A59qfoWo/2r9rvIGZrJpQtuzKV3gKNzDPbOcfQ0Aawzjnk0wrkEAlSR94Cn55xRQAxlYkAHg5ye9Ki7Y1UszbRjLdT9adRQAU2R1jid3OFUEn6U6qmpzGCwlcEDouT7kD+tAEFjaxi9+0RwrHGkKxoM5ZTgZB9MAKPzrSrO0j5pL+RW3RvdHZz0wqqf/Hga0aACiiigAooooAKKKKACiiigAooooAoX0AF5Z3gA3RPsY/7LDHr67fWr9UtY3jSbl4iQ8aGRcDJJX5gP0q6DkZFABRmiqrNKzuGfaCvGwA7Tnr656dscGgDL8RWKapbQoJ4kxOqq3lbmBDDOCORyO2OnXitQQqirHIvnKqZG8ljkDHAOfXrnvUK284mMpWNSqfLtPy7mYkkj24575NTTRwTvs8xRNlX+9kgAg9PTIHtQAw+bctFnYsDLl4pFG7kZwR2IO39fan2d0lyjMqFFDbUyMbhtB7/AF6diDSF4p5Eng8qfyyUJXDFc4zg9vUiooILiC9YKESy2sBGvJLk7t2ew5YY+lAGhRVJMQwRQRTlWaUBfMO44zuK9f7oYfhVwMrAEEEHpigAPHJNKDmkxnqARS96ACsTUrP+3LuawlmkjtIFRm8pwGaTO5fyAB98j0rbNU9OVitxM+7M07NhhjAGEGPbCg/jQA/T7GDTrGK0tgRFGMDcck5OSSfXJqzRRQAUUUUAFFFFABRRRQAUUUUAFFFco+uy+INUutH0cXFuts2JNRXBVGH8IHRsnjn3PagB+reLra1vEtfs5ktmnWCed22hNxI4X7zdDz09zXTqu1QuScDGTWLq+jWl3pdx56JLO0O0zP8AKSRj5sjpyM8dKv6Ndm+0ayu2xumgR2x0yQM0ATXbTLbSG2UNNtOwHpntn2psaCJZfIPmPk5Vn/i68nt1H4YqLWL5dO0ue8fP7pcgAZ3NkAD8SQPxpNLaZrYS3XySzZk8vzVkwDjuAOnA4yPc0AZe3xfK0jgaPCrnCxSGSTYv1GMk01Lfxgsm83GhE42/6iXp/wB9fpXSYA5phlRQSzqABnJOOKAMmSy1+UAHVbCLnkxWDBsdwCZTj8qiks/E8Nwr2uqWNxF0aK4tiv47lPXrW5uz93kZxS5yAVwc0AUmFwLNll8iK6k+6Y/mG/HBGcZ6U+CHy5A0IVEclnTacknJJ68ZOPXv61DrNnZ6jaCC9Lgb1dGRtrK4OQwPqOv51BpU14l49rcq00Jj8y3uwQ29c4KsR3ycj1B9QaANiikzniloAD0OK5m98aaRpWtSaTdpPCYgv70R5jGRntyPyrpqwdO0+OTWNbnlDFpZVTBGML5SjI+uaANaxvrTULcXFjcxXER4DxsCM+n1qxXPHR7HQo7vUdO0rfdZDLHb/IWGAuBtAGO5HPc1Hoet6jIkdvf2kkt2x3Mscezy4+mSThSQc5xjvgHuAdLRSB1ZmVWBK/eAPSloAKKKKACiiigAooqtqN2bGxluVgluGQfLFEuWc9gKAMvxBr1lYzQ6ZLFcXFxejYIrf7wUnBYkcjr254pdHtdN03/QdAWAKkn+kruyydRk9ycjHNYWgW97HFea7dW0ra7eo4t4bgEbQBkLjsOB6V1VjbwabbL5pgSedt00iqE82UjJP480AOFzA8l3aqoc2yLvjxknIJA568CpNNuY7zTre5hjMccsYZUI+6MdKx5pHstWub6WJx9rs41WNCC/mBiAg9/n69PyrT0W1ks9HtLaZQskcSqwznB7896ALVwnmQsoRXJHCt0J7ZqgbqKyk2XM6qzL5rbnLBckA8novp+PTFaZ6ViyW6w6hMdPhtjfO4ndpeWZcYKg5yM9AcAD09QDT8yQw7gFMhwQuf8AOP1/Gs1E1O8vG3Ti1twCGCw5djllwC2QV4znGTkdKdNfQW9jJfkO8UG9mVCysRHkNwevzDvgcgk1dRpJI0VSsbBf3gVgxViM+nPUdfWgChbafqouw17qYmtSFJiWLYQ4bdkEHpwBjnjj621BhtVEG2IrJtKlWYctjA9B0x2Hpip4HK2sPmthyi53nBJxz+PWqGo3EqzW8Vurv5zlCRGHiT5cgP8ANkdiCPpxkUAV9RkkQXLTWsk0Jj4kURlFAzlm+ZSCOeh44PByKvWEUDyfarfY0bRhYpAxbcvc5PY4HQnPXvSNLKt/KvlblSFT8vYE89OfXAHXaRgcErbPKNRdDvlidNyu0RTy8Y4zjnOc9e3SgDQooooAKrWs0cslwsf3o5dr/XAP8iKsmqE1gVnku7BxDcyYMmeUlwMDcP6jmgC3KZA0florAth8tjAx1HHJ6cVjXv8AaEOoXE8Vogj2YSUHeGHcMo+YHPpxwKLu8nO8SCXTblFwszkNA5OcDOcEfgCPatSzu4r1ZPKbPlOYpVIwVYYyPyI+uRQBzC3FvoOk2dxpNlbslwCslzKzZ3k8BmxkjJPXGAPwrqLK7S7iJA2SIdskZIJjb0OKz75ZLaW3tIbeM2DRupjWAthgOAccYOe47VTE9xYSQahMhMe/yLqRSdvl8hZCMAcN1I6Akn2AOkooooAKKKw9S8U6bY3i2ETPeX7nCWtsNzZ9z0X8T0oA3KwNR1eC51IaVYauba9Ubjtt/NXAPO4kYHQjqO9O3eIZp4mX7LFFKdzrnIiQDoDjLOSev3QBxnqaN1ZX+keHtRnQ/btRuwsZVBtCk4RVX/ZBbv8ApQBqS2tzd6rYrMzeVYjzWlX5fOkKlQAB2wWJHuKkvwbm8js7ixaS0Zd3ng/ckBGOOo+tNsoZdK0OCwicXF3b24AXdy2OCcE9M1pRussSuAcMM4IwfyoAxtS+XxTopkzsZZ1Q4PL7c4P/AAEN+ta1vMJ/MK/wSFOnpVTVrWSax2W2POhdZ4i5z8ysGAPfBwV+hNTaXcWt5Yx3lkwaG4HmA5zyev49vwoAtMcKScnHPHWs+9YxkBPkklLCKVzkRsVPzYJ6cY/H3NaBz2xTXjSVdsihhnOCMjNAGXDHam1it4ZUlEabWZsM0uSRgse5ZSW75FT2hFrP9lcu80zSzM204wGHHPoGUD6H0pkGkR2yiGERi38zzDGy5ydwYfkRnPr2qwlrJvVpJpCgjC+WTnnOc56kn+nHegCC2u7e8WZF3bjuBxk8YBBB6ch+D/hgQILm2hg/tCRpGKspZGwoOVCjHU5xu3dvm7VYuNKS4S4V5ZM3G0MePlQY+UcdOvX+8asNbSMrq87EMzYwBwp7c5zigDP+0W8kyxyI4adVkiLttk75UY+YFecj/ax3xWhZQiNZHwo81y4whU4PqD3/AJ9cc1MkSISVUAsck9yafQAUhOMcUAg9DmloAKbGSVyQRkng0rjKEZIzxkUKMKB6UAQ3v2b7HMb0Rm2VC0nmDK7RySfyrmtEu21DzdRSWW2sIo0leJV+84Xn5up+QLkfT6U/xDLJrt6fDthIRGuHv5l5CDqqfUkZPsPeugsLSOytVgiGFBJA9MnOKAIIL86hYQXGnI5juVbZLIuBHwcFlJBIz2H6VlW13qkesQ6LfRQXVu0DGW4HfLNhSP8AdAz1rZgS9j1C4M8qSWr4MIAw0fHIPHI75zWJqsUNp4nS6e/ewiuLUvK4dQrmJ0IBB77WI+n0oAZq3iceGNYgtNTjJ064X9zcLktHjAIYdx0OevPeumgnhuYVmtpUlicZV0YMrD2IrE8S6jLaW+m3Nh9lkM95HAJJF3gK+RlSD9Kr3nhvUnuC+ma3/Z0LctDBBhS3dsbupoAmvzqt/JttVt5bdch40vDGM5IwWVSx6dBtx0+aqmnWFrplxc2PhnTUhuOPtF07FkjP90MxyT7AYHeumSOKHc6xJHxyQAMinLbxLGEVAEDbsD1znP580AZeo6I+pxxR3Oo3iwogV44X2eae5Yjrn06VUi0SW01OyjtFhj0xHaRoiu5w4A2ks2SccjqOD9K6OmS+Z5bCIqJNp27hxn39qAKE9pNLfw3jLtktS/lmNv8AWRsOVYH8D9VFWgzNMvkOhjBIlGckH/GoLi7nfS559PRJLmIMBG395eq8c/5FRu93HqTG5WBNPEJczCTDqykdenGP5daAKkV5eahpzpIjWk97LJDEpGWiRcqXIP0JHbLLWF4W+3+Hr++024T/AIlttJgMeSin7sgxngjG7pjr3NaF1Lf3OqrJd3FqmjXFuvlTRsQGBI3fMMFSwORzjAHUiunVUXkLjt0oAejK6B0YMrDIIOQRRtGQccjvWS1tdaS2/TIhPZ5y1pkKyf8AXI9PX5TgehHSrNhq9lftsglKzAZaGRSkij3U80AXhnvRRRQAUUUUAFFFFAB9KKKo3+rWtiRG5eWdvuwQrvkb8B0HucD3oAuscDOQMetYNzqs+rSy2Hh6ZAYyBPe4DJFnqE7M/wCg+vFRmy1PX33aoWstNPIso2xJJ7St6Hn5R69a07u4s9B0wMkGyJSEjhhQZZmOAqj1JNAEdnpcGmW0NpZR/K8gMsrYLP1JLHuSePxrSBGSueRya57UfEyadrenaVewSxS3hj2yqodCxJBXOR0O0Zx36VtsXS6LyTRLBsGFPDbucnOcYx2xQAl5cJb25nYbljG7Pt/jzWT4ourez0ePW2txdLZnzEUY+YOpTqeg+YH8K0bN7ZbIjTQJkUttAfIZs5PzH3PWszUHvr/wVqAvrdLa5a3mDLnKjGemfagCtqNxa674Ijv57W4itgY5zHCfmCqwJxjGRwf5+lb0V0ojHk2VzsIBH7sL+hINYOjXUsPw2iuLXak1vZllON4yoJzg464z+PeumiDtChdwWKjJC4BP0PSgBxQMhVvmBGDnvTicDmiql+iy2s8DjKOm1sHHDHBoAda3UFzLceQ6uYn8t8HocA4/8eqZJVeR0UqShwcHkcA8/nUVlZ21lAIbSFIo14wo9OOfXpRFZW0N1PcxRKs0+PMcdWwMUAY2s6ZfQyzXmiXXkSXRVZ4mHD8bQwPVWx3HHtWP4gVtNl0XxBa2rx75MXiEs7bZFBIOfQAjOPT6V2kkMcpXzEVtrBlyOh65rltGjHifwSItWJclyd6YVgVO4Eenp9KALkdvHc/2jodzJlJP9LtH3chGbdkf7kmfwK1Joupskc1jqVwGvrQrHIxi2eYTyGHYg+3p71V8P2a22iWrmaaZ7efbE8rAlFZgpUEAcY7H29BWrNoenT60mrzW6yXaRiNWbkAAkg49eetAEthqUV3NcWwyJ7aTy5EOePQ/Qgg07UNMstTRVu4FdkOUccOh9Qw5FZ8rLH4sgjSNF+0W7SSOBhiUIAye/B759sVtqMADOaAMz+z9Rtk22OqswH3UvIxKAPTcCrH8STT/AD9VjYCSxt5Vzy0VwQf++WX+taNFAGdJqVxETv0i9YZ4MZjbP/j1Oj1SN1Be1vYyRna1s5I/IEVfooAzzq8GDttr9mHQCzlGfxKgU2PUL24z5Gkzx+jXMixj9Cx/StKigChNZ3d0xE180MJ/gt12sfYucn8sGprPT7SxVhawJGXO52A+Zz6k9SfrVmkJxQAtUtSjlktpJLdInuIVLweYNw8zBxkVNO7JLEFP+sJU+3yk5H5VKRhcZP1oA5nWEg1bRZJrhYotW0pRcDAJMMqjdxkZKEr16H6itHUTbCbTpb5R5wJwVBIXjLc9hxnnsKyPHqiDQ45ohtlluI45HHVlfCsD7EAAjpW9qLrZWEccMMXlF0h8sr8oUkKRj6UATW91byxRm0w8boWTYMDGaYAhW6t5p4jkElVxlVI5JBrD1XXrqy1ldNt44FhZo0B2ncobrjnH6VpQaXaWX2ddjXEkoaF5rhjJIyMpYgseoyo4oAoeGm0y58NXVv4evnkjzKEJHzQlskDa3AAzwMY/WtHR/wC1DpVsL9YEulQCVdxbDY7nPWuX+G7wtPdwpZ20RhQKJEQh2GT1OeetX/E+i2d7qizSou8xgH90jZ5P95SaAP/Z'
SELECT
    CAST(N'' AS xml).value('xs:base64Binary(sql:variable("@Base64String"))''varbinary(max)')
GO

 

Converting column values from Base64 to VARBINARY

For this demo I will use one of the option above in order to fill the table with the BASE64 values of the images.

UPDATE AriTestTbl SET AvatarBase64 = s
from AriTestTbl
cross apply (select AvatarBinary as '*' for xml path('')) T (s)
GO
 
SELECT FROM AriTestTbl
GO

and here we can see example of converting the data

SELECT AriTestTbl.AvatarBase64, CAST(N'' AS xml).value(
    'xs:base64Binary(sql:column("AriTestTbl.AvatarBase64"))'
    'varbinary(max)'
AS column_varbinary
FROM AriTestTbl
GO

Conclusion

As you can see in the above demo it is very simple to convert both direction between Base64 String and VARBINARY, using built-in XML and JSON features.

More information


Tags: sql server , T-SQL , BASE64 , XML , JSON
Categories: SQL
Location: Blogs Parent Separator Public blog