Posted by enno on 02/07/06 16:55

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

[Back to original message]