October 5, 2009 at 10:06 am
Hi guys,
I was trying to find an answer on my question all over the internet, but may be I was googl skills for SQl server not as good.
I have a Sql server 2005 database that has a field of ntext type..
Some records contain a null char at the end of a string..
1. How can I select these records that contain null char in a field?
I've tried different approaches including PATIndex('%\0%') and replace ('\0', '')
nothing actually worked for me
2. How can I strip out null char from the NTEXT field and save it back to the same field...
Basicly I want to clean up my database.
Any sugestions?
Thank you
Alek
October 6, 2009 at 7:41 am
I haven't used ntext but the ASCII code for the NULL character is 0 so you could use CHAR(0) or probably better in your situation NCHAR(0).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 6, 2009 at 8:25 am
Thank for your reply,
yes I was thinking of it, and it was one of my test,
but it didn't help aether.
I run
select postid, Body, Datalength(Body)
from posts
where postid IN( 1,2,3)
and Body like '%'+nchar(0)
it brings me back all the records that has or hasn't 0 at the end of the string
it feels like Sql server was developed in C or C++ where \0 is treated as end of the line.
so when finally the query comes to the engine, the engine treats it as a '%' string.
October 7, 2009 at 9:24 am
NTEXT has numerous limitations. I would cast the NTEXT column as NVARCHAR(MAX) and then use the REPLACE function to remove the offending (C) null.
Having been down this road before with an application that could write to both IBM's DB2 and Oracle, the problem is usually caused by the C / C++ application program. Where the application interfaces with the database layer, it normally needs to set a length parameter. The error is usually caused by the incorrect C function that computes the length of the data (i.e., ignoring the C terminating null). SQL Server is not appending the null.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply