In Sql Server, Replace A Char(0), The Null Character, Embedded In A String With Its Hex Code

What is a construct in SQL Server T-SQL that will replace a Char(0), the null character, embedded in a string with its hex code? I.e. REPLACE('t'+Char(0)+'t', Char(0), REPLACE(mas

Solution 1:

What you want might be this.

SELECT REPLACE('t'+Char(0)+'t', Char(0), CONVERT(VARCHAR(10),REPLACE(master.dbo.fn_varbintohexstr(0), '000000', '')))

You need to convert it explicitly to VARCHAR

and if you want the full expression remove inner replace

SELECT REPLACE('t'+Char(0)+'t', Char(0), CONVERT(VARCHAR(10),master.dbo.fn_varbintohexstr(0)))