How to convert the content of an Image to text in SQL Server 2019

Question

I'm querying a database and payload data is stored in an image column in Microsoft SQL Server 2019. I want to read the actual text value of this column.

I wrote following query:

SELECT CAST(PAYLOAD AS NVARCHAR(MAX)) FROM MY_TABLE

It gave me the error: Explicit conversion from data type image to nvarchar(max) is not allowed.

It doesn't seem to work and I'm looking for a way to convert this value to a text in a select query.

Answer 1

You can use a double conversion:

SELECT CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), PAYLOAD)) AS PAYLOAD_TEXT 
FROM MY_TABLE

The whole example:

-- Create sample database
CREATE DATABASE img
GO

-- Create sample table with image type
CREATE TABLE MY_TABLE (
PAYLOAD IMAGE
)
GO

-- An example value as image
INSERT INTO MY_TABLE(PAYLOAD)
VALUES(0xFF)
GO

--Resolution for your problem
SELECT CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), PAYLOAD)) AS PAYLOAD_TEXT 
FROM MY_TABLE

Note: Image type is deprecated.