Convert BLOB to String(Text) SQL Server

JosephTucker

I have a BLOB stored in SQL Server. I've managed to convert it utilizing C/AL code, but I need to determine a way to convert it via SQL Query.

the C/AL code is:

---

OnAfterGetRecord()
CALCFIELDS(Rec."BLOB_FIELD", Rec."OTHER_BLOB_FIELD");
"BLOB_FIELD".CREATEINSTREAM(Istream);
Istream.READ(TextData);
CLEAR(Istream);
"OTHER_BLOB_FIELD".CREATEINSTREAM(Istream);
Istream.READ(OtherTextData);

//Istream is of type InStream

---

The above code seems to work just fine. However, for other purposes, I need to convert this within a SQL Query.

I have tried:
SELECT [No_], CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),[blobField] )) FROM [foo$blobtable]
but I get data that does not seem to make sense

Juhl

It's compress by NAV.
Turn off compression on the field and you can use it from SQL.

Juhl

But I would never do it outside NAV.
With .NET everything (almost) is possible from NAV with Job Que

Heroeskitchen

Kind of an old post, but for anyone still interested, you will need to set the Compression property on the field to No in NAV then use the following:

SELECT [No_], CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),[blobField]),0) FROM [foo$blobtable]

Artem

Try this CLR function github