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
5  6707 
(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
Would the database setting of ANSI Nulls have an effect on this
behavior? In 2000? In 2005?
-- Mark D Powell --
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
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.
(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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 do this on the server side in the SQL area of the ASP page by writing a function (with some great...
|
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?
--
|
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
|
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:
#include <stdio.h>
#include <string.h>
int main(int argc, char** argv)
|
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)
| | |
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.
|
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? Thanks in advance. ~ CK
string getComments()
|
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
|
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 found.
Anyone and idea what is happening here?
Thanks,
john
|
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 can accomplish this task.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
