Reply to Re: REPLACE function doesn't work with null-bytes

Your name:

Reply:


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

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация