4

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).

Community
  • 1
  • 1
Mark Hurd
  • 10,665
  • 10
  • 68
  • 101

1 Answers1

3

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)))
ClearLogic
  • 3,616
  • 1
  • 23
  • 31
  • Just for completeness the final SQL:`with m as (select 0 as n union all select n+1 from m where n<31) update Messages set MessageText = replace(MessageText, char(n) collate SQL_Latin1_General_CP1_CS_AS, CONVERT(VARCHAR(10), replace(master.dbo.fn_varbintohexstr(5), '000000', ''))) from m where n not in (9, 10, 12, 13) and MessageText like '%' + char(n) collate SQL_Latin1_General_CP1_CS_AS + '%' ` – Mark Hurd Jul 20 '12 at 02:39