Ignore Char(0) when retrieving data via SqlClient
A while ago we switched from using OleDB to SqlClient in our .NET environment. We have recently noticed that when retrieving data that by chance had a string ending in a CHAR(0) character, that OleDB would return the string without the Char(0), while SqlClient also returns the Char(0).
We prefer the former (Do not want the Char(0)).
Is there a connection string setting or a Database Collation that will allow SqlClient to ignore the Char(0) if it is present?
Or any other idea?
Using REPLACE is not an option as we have this problem in numerous tables.
We prefer the former (Do not want the Char(0)).
Is there a connection string setting or a Database Collation that will allow SqlClient to ignore the Char(0) if it is present?
Or any other idea?
Using REPLACE is not an option as we have this problem in numerous tables.
Thanks!
You should have elaborated your environment and scenario for a precise answer. The more details you give, the better answer you may receive. Moreover, How did you find the OLE DB is not returning CHAR(0)?
I think you have missed the key factor that char(0) is the NUL terminator for .NET. Since you may be dealing with Strings OR Character arrays, you would not have faced it while debugging or so. My understanding is, if you do not want to handle it at database level, then you need to handle it at the application level. AFAIK, there is no such workaround available at database level unless you REPLACE the said char.
@Dabas Robbin is absolutely correct, char(0) does not belong in the database. Per Phil Factor: > TSQL string functions can't reliably handle the CHAR(0) character. Phil has posted a [very good answer][1] to the similar question, please take a look. Other answers to the same question confirm that char(0) should not be present in the database. [1]:
http://ask.sqlservercentral.com/answers/32436/view.html
Thanks: > How did you find the OLE DB is not > returning CHAR(0)? The data has not changed in ages. We only became aware that the data has the null characters when we switched to using SqlClient. A recent test using both methods confirmed this fact > I think you have missed the key factor > that char(0) is the NUL terminator for > .NET We are aware of this, that is why we want to fix it! Thanks for answering, thanks for the link. We plan to fix it at database level, although it seems it will be a major job.
@Dabas As Robbin already pointed out, you can still use **replace** function at the database level, this will work. The biggest problem with char(0) characters present is that they force the values to appear chopped off when selected. For example, if you have a varchar value of **'Hello' + char(0) + ' Bye'** is will appear simply as **Hello** when you select it. The bottom line is that you should fix the problem with introduction of char(0) to DB at the app code level and also have a one time cleanup job to cure the pre-existing condition:
declare @test varchar(50);
select
@test = 'Hello ' + char(0) + 'Newman' + char(0) +
' Hello ' + char(0) + 'Jerry' + char(0);
select
cast(@test as varbinary(50)) RawValue, -- to visually view char(0)
@test ThisDoesNotWork, replace(@test, char(0), '') ThisDoes
where @test like '%' + char(0) + '%';
Oleg
I ran into this before. Chopping off everything after a hard nul terminator is the theoretically correct response by the database. In my case, I used a script outside the database to strip out all the NUL characters prior to them hitting SQL, but as pointed out that is hardly the only approach.
After further investigation, the main problem was that the database was using a SQL collation Something like > UPDATE TableName set FieldName = REPLACE(FieldName, CHAR(0), '') WHERE FieldName LIKE '%' + CHAR(0) + '%' causes SQL Server to hang and eventually time out. The correct solution is to convert to a SQL collation both at the replace and at the where > UPDATE TableName set FieldName = REPLACE(FieldName COLLATE SQL\_Latin1\_General_CP1\_CI\_AS, CHAR(0), '') WHERE FieldName COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS LIKE '%' + CHAR(0) + '%'
