Working with an integration to the ERP system Dynamic NAV from Microsoft I had a request to be able to put large text into a blob (image) field as Dynamic NAV in the version that I was integrating to couldn’t create large (over 250 characters) in other way than in a blob field.
It is easy to put text into the field. You just do a cast to image field like this:
declare @longtext varchar(1000)
set @longtext = ‘very long text and so on….’
update tableX set imagetext = cast(@longtext as image)
where id = 1
If you want to extract the text again from the field is a bit more complicated, but isn’t that hard once you know it. You first have to convert it to varbinary and then you can convert it to varchar. Like this:
select convert(varchar(1000), convert(varbinary(1000),imagetext) from firstdb.dbo.tableX where id = 1
A weird thing that I experienced working in SQL Server Management Studio was if a ran this query when I am working (like “use firstdb”) in the database, the local special characters was returned without any problems. If I instead was in the master database (like “use master”) and executed the query it return some different characters when it came to the locale special characters. As far as I can tell the collation was the same in both the databases.