Posted by Erland Sommarskog on 02/08/06 01:02

(enno@berlin.cortex-software.de) writes:
> We have a problem with null-Bytes in varchar-Columns, which are not
> handled correctly in our application. Therefor we try to filter them
> out using the Transact-SQL REPLACE function.
>
> The Problem was, that the REPLACE-function didn't behave the way we
> expected.
>...
> The Null-Byte replace destroys the whole string. This behavior occurs
> only on some of our
> databases. The others work correctly.
>
> Is it possible that it depends on some server setting?

I've seen this before, but I was a little puzzled when you said that
it worked on some databases.

Playing around, I was lucky to find that it works if you have an SQL
collation. So in your script, change the crucial line to:

print replace(@txt COLLATE SQL_Latin1_General_CP1_CI_AS, char(0), '*')

In SQL 2005, the string does not get destroyed, but neither does the
NULL get replaced. Since this is inconsistent, I filed bug for this on
http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK45444
You can vote on it, if you like.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: one for the SQL experts - dare I say TRICKY SQL!
• Prev in forum: Re: Class does not support aggregation
• Thread view: Re: REPLACE function doesn't work with null-bytes

[Reply to this message]