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

sql-server replace hex nul
Mark Hurd picture Mark Hurd · Jun 8, 2012 · Viewed 14.8k times · Source

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

Answer

ClearLogic picture ClearLogic · Jun 10, 2012

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

Related questions

UPDATE and REPLACE part of a string

I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column. Example of Table: ID Value --------------------------------- 1 c:\temp\123\abc\111 2 c:\temp\123\abc\222 3 c:\temp\123\abc\333 4 c:\temp\123\…

Read More
SQL Server: How do you remove punctuation from a field?

Any one know a good way to remove punctuation from a field in SQL Server? I'm thinking UPDATE tblMyTable SET FieldName = REPLACE(REPLACE(REPLACE(FieldName,',',''),'.',''),'''' ,'') but it seems a bit tedious when …

Read More
Replacing a specific Unicode Character in MS SQL Server

I'm using MS SQL Server Express 2012. I'm having trouble removing the unicode character U+02CC (Decimal : 716) in the grid results. The original text is 'λeˌβár'. I tried it like this, it doesn't work: SELECT ColumnTextWithUnicode, REPLACE(ColumnTextWithUnicode , 'ˌ…

Read More