REPLACE function doesn't work with null-bytes

[enno]

Dear Community,

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.

Following Example demonstrates the behavior:

declare @txt varchar(512)
declare @i int
set @txt = 'hello ' + char(0) + 'world'
print @txt
set @i = 1

while @i <= len(@txt)
begin
print str(@i) + substring(@txt, @i, 1)
set @i = @i + 1
end

print 'Length: ' + str(len(@txt))
print 'trying to replace null-byte:'
print replace(@txt, char(0), '*')
print 'replace Letter h'
print replace(@txt, 'h', char(39))

-- end example

Output:

hello
1h
2e
3l
4l
5o
6
7
8w
9o
10r
11l
12d
Length: 12
trying to replace null-byte:
*
replace Letter h
'ello

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?

Thanks
Enno

[Erland Sommarskog]

(en**@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/Produc...ckId=FDBK45444
You can vote on it, if you like.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Mark D Powell]

Would the database setting of ANSI Nulls have an effect on this
behavior? In 2000? In 2005?

-- Mark D Powell --

[Erland Sommarskog]

Mark D Powell (Ma*********@eds.com) writes:

Would the database setting of ANSI Nulls have an effect on this
behavior? In 2000? In 2005?

I haven't tested, but I find that very unlikely.

The effect of the database setting ANSI_NULLS when it's ON is that it forces
the SET option ANSI_NULLS to be in the ON position, even though the process
has it OFF. If the database setting is OFF, it has no effect, but the
setting of the process determines.

And to make it even messier, when you run a stored procedure, what counts is
the setting when the procedure was saved.

As far as I know, ANSI_NULLS has no effect on the interpretation of NUL
characters. ANSI_NULLS determines how things like "IF @x = NULL" works.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[enno]

Thanks for the answer.
We have cleaned our data using the sql-Collation.
I think it's not wise to have Data with NUL-bytes in a
varchar-Column, because other Transact-SQL-functions have also
problems.
For example the LIKE operator is also inconsistent.

SELECT 1
WHERE CHAR(0) LIKE '_'

Yields:
<nothing>

SELECT 1
WHERE CHAR(65) LIKE '_'

Yields:
1

By using the collation you suggested, the LIKE Operator worked as
expected.

There must be some enviromental COLLATION setting, which is
database-specific, but i didn't find it in exported-scripts from the
enterprise manager.

[Erland Sommarskog]

(en**@berlin.cortex-software.de) writes:

There must be some enviromental COLLATION setting, which is
database-specific, but i didn't find it in exported-scripts from the
enterprise manager.

In SQL Server there is first a *server collation* which defines the
collation for the system databases, and the default for new databases.

There is also a database collation, which defines the default collations
for new charcater columns in the database. If memory serves the database
collation also sets the collation for variables. However, temp-table
columns get their default from tempdb. If I remember correctly, table
variables gets their default from tempdb in SQL2000, but the database in SQL
2005.

When you create a table column, you can always explicitly set the collation.

And finally, when you have a character expression, you can always cast
to another collation.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx