In SQL Server, replace a Char(0), the null character, embedded in a string with its hex code
hex, nul, replace, sql-server
Solution
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)))
Problem
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(master.dbo.fn_varbintohexstr(0), '000000', '')) ``` does not return `'t0x00t'`, what does? (This does already work for 1 through 31.) This question has answers explaining the problem is the Collation. This answer shows `master.dbo.fn_varbintohexstr(0)` will return `0x00000000`. When I manually simplified the inner `Replace` to `'0x00'`, just adding a `Collate` clause did get it to work, like the answers to the above question suggested, but I can't find a version that works for the full expression (which then could be used for all n, 0 to 31, skipping 9, 10 and 13).