Convert BLOB to String(Text) SQL Server

JosephTuckerJosephTucker Member Posts: 1
edited 2017-06-16 in SQL General
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

Answers

  • JuhlJuhl Member Posts: 724
    It's compress by NAV.
    Turn off compression on the field and you can use it from SQL.
    Follow me on my blog juhl.blog
  • JuhlJuhl Member Posts: 724
    But I would never do it outside NAV.
    With .NET everything (almost) is possible from NAV with Job Que
    Follow me on my blog juhl.blog
  • HeroeskitchenHeroeskitchen Member Posts: 4
    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]
  • ArtemArtem Member Posts: 2
    Try this CLR function github
Sign In or Register to comment.