REPLACE function doesn't work with null-bytes


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

.

Relevant Pages

  • Re: Problem using SP_TRACE_SETFILTER - it doesnt seem to apply th
    ... seem to apply the filter. ... When I open the trace file in Profiler, ... > declare @rc int, ...
    (microsoft.public.sqlserver.server)
  • Re: how do you use a filter in programming
    ... I didn't even see the extra letter in the "str" string. ... declare variables or even pass them. ... your references to FilterOn and Filter are incorrect: ...    Dim stDocName As String ...
    (microsoft.public.access.modulesdaovba)
  • Trigger on INSERT and UPDATE issue
    ... I would like to fetch the updated row in order to filter those out too. ... DECLARE @rows int ... DECLARE @AlarmState int ...
    (microsoft.public.sqlserver.programming)
  • Is there difference between the two ways?
    ... In my project there is a source class, which declare a Filter, the ... The error msg is: ... Continue?(Cancel to debug) ...
    (microsoft.public.win32.programmer.directx.video)
  • Re: Class properties and object properties
    ... Python does not ... declare *instance* variables the way you do. ... Both were class-level variables, as far as I can see, and therefor a ... And good remind on default arguments:) ...
    (comp.lang.python)