In SQL Server, replace Char (0), the null character embedded in the string with hexadecimal code

Question

What is a constructor in SQL Server T-SQL that will replace Char(0) , the null character embedded in a string with hexadecimal code?

those.

  REPLACE('t'+Char(0)+'t', Char(0), REPLACE(master.dbo.fn_varbintohexstr(0), '000000', '')) 

doesn't return 't0x00t' , what does it do? (This works already from 1 to 31.)

This question has answers explaining that the problem is sorting.

This answer shows that master.dbo.fn_varbintohexstr(0) will return 0x00000000 .

When I manually simplified the internal Replace to '0x00' by simply adding the Collate , it made it work, as did the answers to the question above, but I cannot find a version that works for the full expression (which could then be used for all n , from 0 to 31, skipping 9, 10 and 13).

Answer 1

What you want may be like that.

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

You need to explicitly convert it to VARCHAR

and if you want the full expression to remove the internal replacement

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