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

Question

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

ClearLogic · Accepted Answer · 2012-06-10 08:59:19Z

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

edited Jun 10, 2012 at 8:59

answered Jun 10, 2012 at 5:31

ClearLogic

3,7521 gold badge27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Hurd Over a year ago

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       + '%'

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related