How to compare two ntext fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to compare two ntext fields

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2004-01-07 : 06:18:14

Hi,
I have a one table with a ntext field and I would like to find if one record has a duplicate. It is assumed that the field can not be converted to varchar.

I tried to compare the pointers but it doesn't work:

DECLARE @ptr varbinary(16)
DECLARE @ptr2 varbinary(16)
select @ptr=TEXTPTR(OBJ_FULL_CODE) from A_DOC_Obj where REC_NUM=2120
select @ptr2=TEXTPTR(OBJ_FULL_CODE) from A_DOC_Obj where REC_NUM=1851
IF @ptr=@ptr2
print 'Same stuff'
else
print 'Not the same'

The field are the same but I still get 'Not the same'
Thanks, Paul

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-07 : 07:56:44

Your code is comparing pointers, not values, and will never be the same under SQL Server. Without converting the ntext column to nvarchar or varbinary you cannot compare it in SQL Server.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-01-07 : 08:01:06

Would the following work:

create table test ( id int identity(0,1),data ntext)

insert into test select 'same'
union all select 'not same'
union all select 'same'

select id
from test a
join
(
select substring(data,1,datalength(data)) data
from test
group by substring(data,1,datalength(data))
having count(substring(data,1,datalength(data)))>1
) d on d.data = substring(a.data,1,datalength(a.data))

drop table test

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-01-07 : 08:13:41

I don't think that works on large Ntexts columns ehorn, Try it with your insert as

insert into test select 'same' + replicate(' Repeated:', 1200)
union all select 'not same' + replicate(' Repeated:', 1200)
union all select 'same' + replicate(' Repeated:', 1200)

to see what I mean. The problem is in the DATALENGTH function maxing out at 16000 (on my test data). I tried it in the form of a stored proc where you pass in the rec_num of the record you want matches for. The returned value(s) are all records where the ntext column is the same as that of the requested rec_num.

CREATE PROCEDURE user_CompareNText
@Rec_Num INT
AS
BEGIN

SET NOCOUNT ON

DECLARE @Results TABLE (id INT, iteration INT)
DECLARE @TextStr VARCHAR(8000), @NtextLen INT, @nLoop INT, @nLength INT, @TextString1 VARCHAR(8000), @TextString2 VARCHAR(8000)

SELECT @nTextLen = DATALENGTH(OBJ_FULL_CODE), @nLoop = 0, @nLength = 1
FROM A_DOC_Obj
WHERE REC_NUM = @Rec_Num
print @ntextlen

WHILE @nLength < @nTextLen
BEGIN
SELECT @TextString1 = SUBSTRING(OBJ_FULL_CODE, @nLoop, 4000)
FROM A_DOC_Obj
WHERE REC_NUM = @Rec_Num
print @textstring1

INSERT INTO @Results
SELECT REC_NUM, @nLoop
FROM A_DOC_Obj
WHERE SUBSTRING(OBJ_FULL_CODE, @nLoop, 4000) = @TextString1
AND REC_NUM <> @rec_Num
SELECT @nLength = @nLength + 8000,
@nLoop = @nLoop + 1
END

SELECT id
FROM @Results
GROUP BY id
HAVING COUNT(*) = @nLoop

END

Raymond

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-01-07 : 08:27:26

Raymond, Using your replicate insert I still obtained the correct results.
What version/sp of SQL are you using?

Mathias
Posting Yak Master

119 Posts

Posted - 2004-01-07 : 08:34:48

This works, I am pretty impressed, I have changed it to:

select a.id, a.data
from test a join
(select substring(data,1,datalength(data)) data from test ) d on
d.data = substring(a.data,1,datalength(a.data)) and a.id=1

to look if the first one has a duplicate. Could you tell me about the command substring(data,1,datalength(data)) data , I understand that data is added to get it working but what does it do?

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-01-07 : 09:07:02

eHorn

SQL2k, SP3. My problem may be in the SET TEXTSIZE default.

I still think there'll be problems in using SUBSTRING if the DATALENGTH(Data) function returns more than the allowed 8k. Do you agree?

Raymond

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-01-07 : 09:38:49

Raymond,

After further examination, Yes, I do agree with you. In fact SUBSTRING() will limit the compare to 4000 bytes ntext due to the conversion of ntext to nvarchar. Mathias should consider your method of chunking the data into 4K nvarchar sizes and performing the compare as Rob and you suggest.

Nice catch Raymond.

Mathias
Posting Yak Master

119 Posts

Posted - 2004-01-08 : 04:08:16

It there no way to compare the memory checksum of the two ntext fields to see if they match? This will avoid looping through each ntext.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-08 : 07:51:08

What is a memory checksum? There are two functions in SQL Server to calculate checksums, but neither of them work with ntext.

If you need to perform this kind of test regularly you should reexamine using ntext and consider using nvarchar instead. You can hold 4,000 unicode characters and can compare it using any method you like. If you have larger values you can split them into multiple nvarchar(4000) rows. Otherwise you'll continue to be limited in what you can do with ntext.

tonylin919
Starting Member

1 Post

Posted - 2008-10-06 : 17:25:10

I realize this is an old post, but it still turns up in an Internet search. Try the code posted here: http://www.weberdev.com/get_example-4870.html to compare two NTEXT fields. Starting SQL Server 2005 this is no longer necessary, but for those still working with SQL Server 2000 it should help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources