ntext, compare

[mcaizpp2]

Hi,

I know that you cannot place ntext data types into variables but I was wondering if anyone knows of a "work around" for this.

The problem I have is that I need to compare 2 ntext data types to check if they are the same or different.

What I am doing - When a user edits a record and then clicks update (via the website) I want to compare the value currently stored in the database with the value from the textbox on the website.

I am using a stored procedure at the moment to do this.


Thanks in advance

 

[ptheriault]

Is this on SQL 2000 or 2005? If you are using SQL 2005 you can replace the ntext datatype with nvarchar(max) will will hold up to 2gb of data. nvarchar(max) and varchar(max) do not have any of the restrictions that a text field has so you can compaire them.

- Paul
- Database performance looks fine, it must be the Network!

 

[mcaizpp2]

Hi Thanks for responding. Unfortunately its a 2000 database. I was thinking of using .net code instead of sql to compare the 2 values but I would prefer to use SQL server procedures etc - what do you think?

 

[ptheriault]

unfortunately, I don't know of any way to get that done in a proc. Maybe George or Denis will know.

- Paul
- Database performance looks fine, it must be the Network!

 

[gmmastros]

I'm a little confused about why you need to check to see if they are different? Why don't you just update the data anyway?

There are some things you can do with nText data. For example, you can quickly determine the data length. If the lengths are different, then certainly the data has changed. Checking the length of the data should be very fast, so I would start there.

Declare @CharCount Int

Select DataLength([!]nTextColumn[/!])
From   [!]TableName[/!]
Where  Id = @SomeValue

Since you are using nText, datalength will return 'double' the number of characters.

Create Table #Temp(Data nText)

Insert Into #Temp Values('12345')

Select DataLength(Data), SubString(Data, 1, 4000)
From   #Temp

Drop Table #Temp

Eventhough there are only 5 characters in the string, DataLength returns 10.

If the character counts are the same, the data could still be different. You could loop through the field, using substring, to grab 4000 characters at a time for comparison purposes.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

The DataLength is a great solution George I don't know why I didn't think of that.


- Paul
- Database performance looks fine, it must be the Network!