Converting ntext / text to hex / binary / varbinary

Question

How can i get this query on SQL server? it's cause an error: This is MySQL version

SELECT HEX(c1) FROM t1;

This simple example work:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT CAST('example data' AS VARBINARY) AS Body2

But this sql dosnt work.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 10 [NewsID]
      ,[upTitle]
      ,[Title]
      ,[Summary]
      ,CAST(Body AS VARBINARY) AS Body2
      ,[LargePic]
      ,[PublishDate]
  FROM [Upgrade_News].[dbo].[News_News]

That's give me this error

Msg 529, Level 16, State 2, Line 6
Explicit conversion from data type ntext to varbinary is not allowed.

1. I wanna just have hexed column as value. how can i do this on SQL Server?
2. What's the correct functio for HEX and UNHEX data on SQL server?

I need to convert ntext to HEX data on select. it's not an integer or short string.

Answer 1

There's no direct conversion from ntext to varbinary, so convert to nvarchar(max) first.

SELECT CAST(CAST(Body AS NVARCHAR(MAX)) AS VARBINARY) AS Body2

ntext was deprecated with SQL2005. Avoid using it if possible. https://msdn.microsoft.com/en-us/library/ms178158%28v=sql.90%29.aspx