Convert Business Central BLOB Value to Text in Transact-SQL with SQL CLR

    procedure GetWorkDescription() WorkDescription: Text
    var
        TypeHelper: Codeunit "Type Helper";
        InStream: InStream;
    begin
        CalcFields("Work Description");
        "Work Description".CreateInStream(InStream, TEXTENCODING::UTF8);
        if not TypeHelper.TryReadAsTextWithSeparator(InStream, TypeHelper.LFSeparator(), WorkDescription) then
            Message(ReadingDataSkippedMsg, FieldCaption("Work Description"));
    end;
    
    procedure SetWorkDescription(NewWorkDescription: Text)
    var
        OutStream: OutStream;
    begin
        Clear("Work Description");
        "Work Description".CreateOutStream(OutStream, TEXTENCODING::UTF8);
        OutStream.WriteText(NewWorkDescription);
        Modify;
    end;

SELECT CONVERT(varchar(max), CONVERT(varbinary(max), [Work Description])) as [Work Description]
FROM [CRONUS Australia Pty_ Ltd_$Sales Header] with(nolock)
WHERE [Work Description] IS NOT NULL

using System.Linq;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.IO.Compression;

public class BcBlobDecompressor
{
    private static readonly byte[] BlobMagic = new byte[] { 2, 69, 125, 91 };

    [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
    public static SqlBytes Decompress(SqlBytes inBytes)
    {
        if (inBytes == null || inBytes.Length < 4)
            return inBytes;

        byte[] firstFourBytes = inBytes.Buffer.Take(4).ToArray();
        if (!BlobMagicOk(firstFourBytes))
        {
            return inBytes;
        }

        using (Stream inBytesStream = inBytes.Stream)
        {
            inBytesStream.Read(firstFourBytes, 0, 4);
            var outStream = new MemoryStream();
            using (DeflateStream deflateStream = new DeflateStream(inBytesStream, CompressionMode.Decompress))
            {
                deflateStream.CopyTo(outStream);
                return new SqlBytes(outStream);
            }
        }                
    }
    private static bool BlobMagicOk(byte[] checkMagic)
    {
        return checkMagic.SequenceEqual(BlobMagic);
    }
}

C:\temp\sqlclr\> C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc.exe /target:library .\BcBlobDecompression.cs

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO  

-- Source: https://pastebin.com/mwi5BidL

use [master]
-- Verify that "clr strict security" is 1 (i.e. ON), and that TRUSTWORTHY is 0 (i.e. OFF):
PRINT CHAR(13) + CHAR(10) + 'Verifying settings...';
SELECT *
FROM   sys.configurations sc
WHERE  sc.[configuration_id] = 1587 -- "clr strict security"
 
SELECT [name], [is_trustworthy_on], [collation_name]
FROM sys.databases WHERE [database_id] = DB_ID(N'YourDatabaseName');
GO

USE [YourDatabaseName]

-----------------------------------------------------------
-- Create asembly and function:

IF (OBJECT_ID(N'dbo.DecompressBcBlob') IS NULL)
BEGIN

	PRINT CHAR(13) + CHAR(10) + 'SQLCLR UDF [DecompressBcBlob] does not exist:';

	PRINT ' TEMPORARILY Altering Database to set TRUSTWORTHY=ON...';
		ALTER DATABASE [YourDatabaseName]
			SET TRUSTWORTHY ON;

	IF (ASSEMBLYPROPERTY(N'BcBlobDecompression', N'MvID') IS NULL)
	BEGIN
		PRINT ' Creating [BcBlobDecompression] Assembly...';

		CREATE ASSEMBLY BcBlobDecompression 
		AUTHORIZATION  dbo
		FROM 'C:\temp\sqlclr\BcBlobDecompression.dll' WITH PERMISSION_SET = SAFE;
	END;

	PRINT ' Altering Database to set TRUSTWORTHY back to OFF...';
	ALTER DATABASE [YourDatabaseName]
		SET TRUSTWORTHY OFF;

	PRINT N'    Creating [dbo].[DecompressBcBlob]...';
		EXEC(N'
	CREATE FUNCTION [dbo].[DecompressBcBlob]
	(
		@BlobValue VARBINARY(MAX)
	)
	RETURNS VARBINARY(MAX)
	WITH    EXECUTE AS CALLER,
			RETURNS NULL ON NULL INPUT
	AS EXTERNAL NAME [BcBlobDecompression].[BcBlobDecompressor].[Decompress];
	');

END;
GO

-----------------------------------------------------------
-- Sign assembly for the database:
 
IF (SUSER_ID(N'YourDatabaseName-BcBlobDecompression-Login') IS NULL)
BEGIN
    PRINT CHAR(13) + CHAR(10) + 'Permission Login does NOT exist:';
 
    IF (CERT_ID(N'YourDatabaseName-BcBlobDecompression-Cert') IS NULL)
    BEGIN
        PRINT ' Creating Certificate in [YourDatabaseName]...';
        CREATE CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert]
            ENCRYPTION BY PASSWORD = 'CiKqB9wv88wYCB'
            WITH SUBJECT = 'Sql Quantum Leap',
            EXPIRY_DATE = '2099-12-31';
    END;
 
    IF (NOT EXISTS(
                    SELECT *
                    FROM  sys.crypt_properties cp
                    INNER JOIN sys.assemblies sa
                            ON sa.[assembly_id] = cp.[major_id]
                    WHERE sa.[name] = N'BcBlobDecompression'
                ))
    BEGIN
        PRINT ' Signing the Assembly...';
        ADD SIGNATURE
            TO Assembly::[BcBlobDecompression]
            BY CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert]
            WITH PASSWORD = 'CiKqB9wv88wYCB';
 
    END;        
 
    IF (NOT EXISTS(
                    SELECT *
                    FROM   [master].[sys].[certificates] crt
                    WHERE  crt.[name] = N'YourDatabaseName-BcBlobDecompression-Cert'
                ))
    BEGIN
        PRINT ' Copying the Certificate to [master]...';
        DECLARE @PublicKey VARBINARY(MAX),
                @SQL NVARCHAR(MAX);
 
        SET @PublicKey = CERTENCODED(CERT_ID(N'YourDatabaseName-BcBlobDecompression-Cert'));
 
        SET @SQL = N'
CREATE CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert]
    FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @PublicKey, 1) + N';';
        --SELECT @PublicKey AS [@PublicKey]; -- DEBUG
        --PRINT @SQL; -- DEBUG
 
        EXEC [master].[sys].[sp_executesql] @SQL;
    END;
 
 
    PRINT ' Creating permissions Login...';
    EXEC [master].[sys].[sp_executesql] N'
CREATE LOGIN [YourDatabaseName-BcBlobDecompression-Login]
    FROM CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert];
';
END;
GO

PRINT CHAR(13) + CHAR(10) + 'Granting UNSAFE ASSEMBLY permission...';
EXEC [master].[sys].[sp_executesql] N'
GRANT UNSAFE ASSEMBLY TO [YourDatabaseName-BcBlobDecompression-Login]; -- REQUIRED!!!!
'; 
GO

PRINT '';
SELECT ASSEMBLYPROPERTY(N'BcBlobDecompression', 'CLRName') AS [CLRName];

/*
-----------------------------------------------------------
-- Clean up:
 
PRINT CHAR(13) + CHAR(10) + 'Cleaning up objects (to be re-runnable)...';
 
USE [YourDatabaseName]
DROP FUNCTION [dbo].[DecompressBcBlob]
DROP ASSEMBLY [BcBlobDecompression];
DROP CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert];
 
USE [master];
DROP LOGIN [YourDatabaseName-BcBlobDecompression-Login];
DROP CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert];
*/

select dbo.DecompressBcBlob(null)
select dbo.DecompressBcBlob(0x02457D5B0B29CF0700)
select dbo.DecompressBcBlob(0x54776F)

SELECT CONVERT(varchar(max), dbo.DecompressBcBlob(CONVERT(varbinary(max), [Work Description]))) as [Work Description]
FROM [CRONUS Australia Pty_ Ltd_$Sales Header] with(nolock)
WHERE [Work Description] IS NOT NULL

CREATE FUNCTION dbo.ConvertBcBlobToText
(
	@i image
)
RETURNS varchar(max)
AS
BEGIN
	RETURN CONVERT(varchar(max), dbo.DecompressBcBlob(CONVERT(varbinary(max), @i)));
END
GO

SELECT dbo.ConvertBcBlobToText([Work Description]) as [Work Description]
FROM [CRONUS Australia Pty_ Ltd_$Sales Header] with(nolock)
WHERE [Work Description] IS NOT NULL