473,581 Members | 2,915 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Feb 7 '06 #1
5 6699
(en**@berlin.co rtex-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_Gene ral_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****@sommarsk og.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
Feb 7 '06 #2
Would the database setting of ANSI Nulls have an effect on this
behavior? In 2000? In 2005?

-- Mark D Powell --

Feb 8 '06 #3
Mark D Powell (Ma*********@ed s.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****@sommarsk og.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
Feb 8 '06 #4
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.

Feb 8 '06 #5
(en**@berlin.co rtex-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****@sommarsk og.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
Feb 8 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
8145
by: Barnes | last post by:
Does anyone know of a good way to use the JavaScript string.replace() method in an ASP form? Here is the scenario: I have a form that cannot accept apostrophes. I want to use the replace() so that the apostrophe is automatically replace with two '' . Reason being--SQL Server does not like apostrophes being sent to database. I've tried to...
12
4031
by: Brian | last post by:
I want to use regxp to check that a form input contains at least 1 non-space charcter. I'd like to only run this if the browser supports it. For DOM stuff, I'd use if (documentGetElementById) {} Is there an object/feature detection I can use to check for regxp string manipulation support? --
3
6161
by: teddysnips | last post by:
In the script below is the DDL to create some tables and a UDF. What I'm interested in is the UDF at the end. Specifically, these few lines: --CLOSE OTRate --DEALLOCATE OTRate ELSE -- @NumRecords <= 0 If I uncommment CLOSE and DEALLOCATE and check the syntax I get a
18
21763
by: William Payne | last post by:
Hello, I need to write a program that opens a text file and scans the entire file for a specific line and when that line is found, a particular word on that line is to be replaced. The new word is given as an argument to the program. I wrote a small test program that doesn't work because strcmp() fails to find a matching line. Here's the code:...
5
2623
by: pembed2003 | last post by:
Hi all, I need to write a function to search and replace part of a char* passed in to the function. I came up with the following: char* search_and_replace(char* source,char search,char* replace){ char* result; size_t l = strlen(source), r = strlen(replace), i; int number_of_replaces = 0; for(i = 0; i < l; i++){ if(source == search)
19
78792
by: Paul | last post by:
hi, there, for example, char *mystr="##this is##a examp#le"; I want to replace all the "##" in mystr with "****". How can I do this? I checked all the string functions in C, but did not find one.
13
18357
by: CK | last post by:
Hi all, I have a textarea control. I am putting it's value in an html email. The problem is that the new lines are being ignored. I want to take the controls value and replace any newline carriage returns, with an html <br> tag. I tried the following function but it doesn't work. Does anyone have any ideas how to accomplish this? ...
5
5471
by: int main(void) | last post by:
Hi all, Following is my attempt to write a string search and replace function. #include <stdio.h> #include <stdlib.h> #include <string.h> /*------------------------------------------------------------------------------------------- | Function name : strrep
2
1570
by: john | last post by:
In a table I have text field A. I would like to replace all the null values in field A to a real value, let's say 'Test'. When I use Find & Replace and I search for 'is null' and I press replace or replace all, no null value is found, so I can't replace anything. However, when I press Find Next in that same dialog then the null values are...
8
17500
by: Joe Cool | last post by:
I need to map several columns of data from one database to another where the data contains multiple spaces (once occurance of a variable number or spaces) that I need to replace with a single space. What would be the most efficient way to do this? I am using SQL2K. I was thinking a function since I know of no single Transact-SQL command that...
0
7808
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8158
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8312
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7917
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8182
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5685
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5366
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1413
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1147
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.