ntext comparison

Question

Post reply

ntext comparison

David P Reynevich

Old Hand

Points: 398
More actions
September 19, 2005 at 10:36 am

#91303

Does anyone have a good solution to determining whether 2 ntext fields are the same?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

I have a solution (Not really sure about the good Part )

Do you need to do this on a whole table ( like a join) or do you need this on two columns on a single row?

* Noel

David P Reynevich Old Hand Points: 398 More actions · Answer 2

Hi Noel

Comparison is on 2 tables - same field. Required functionality is:

if Table(prev).ntextField <> Table(curr).ntextField

process change

Thanks.

noeld SSC Guru Points: 96590 More actions · Answer 3

Select T1.Pkey

FROM

(Select PKey,

CHECKSUM( SUBSTRING( Col_ntext, ( 0*4000 ) + 1, 4000 ) ) + -- Use As many as your max( DataLength ) tells you

CHECKSUM( SUBSTRING( Col_ntext, ( 1*4000 ) + 1, 4000 ) ) +

CHECKSUM( SUBSTRING( Col_ntext, ( 2*4000 ) + 1, 4000 ) ) +

CHECKSUM( SUBSTRING( Col_ntext, ( 3*4000 ) + 1, 4000 ) ) as ChK_Data

from Table1

) T1

join

(Select PKey,

CHECKSUM( SUBSTRING( Col_ntext, ( 0*4000 ) + 1, 4000 ) ) + -- Use As many as your max( DataLength ) tells you

CHECKSUM( SUBSTRING( Col_ntext, ( 1*4000 ) + 1, 4000 ) ) +

CHECKSUM( SUBSTRING( Col_ntext, ( 2*4000 ) + 1, 4000 ) ) +

CHECKSUM( SUBSTRING( Col_ntext, ( 3*4000 ) + 1, 4000 ) ) as Chk_Data

from Table2

) T2

Where T1.ChkData <> T2.ChkData

Notes:

1. This asummes you have more that 4000 nchars on your ntext columns

2. you Can use BINARY_CHECKSUM if you need case sensitive comparison

3. If there is not a one to one PKey you can use FULL OUTER JOIN with a Coalesce

4. I now is not pretty but never said it will be

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Wouldn't it be simpler to use the numbers table to do the splitting of the text fields??

noeld SSC Guru Points: 96590 More actions · Answer 5

True You can simplify the above with a Numbers Table, Some People use views with union all and CHECK_SUM_AGG and so forth. I just posted the Idea on how to go about it.

Feel free to improve on that

* Noel

David P Reynevich Old Hand Points: 398 More actions · Answer 6

Thanks folks (particularly noel).

I've eventually ended up with:

select

distinct T1.PKey

from

Table1 T1

join Table2 T2 on T1.PKey = T2.PKey

cross join TblNumber N

where

(N.Number = 1 and

isnull(DATALENGTH(T1.NTextFld), 0) <> isnull(DATALENGTH(T2.NTextFld), 0)) or

(N.Number <= DATALENGTH(T1.NTextFld)/4000 + 1 and

CHECKSUM(SUBSTRING(T1.NTextFld, ((N.Number - 1) * 4000) + 1, 4000)) <>

CHECKSUM(SUBSTRING(T2.NTextFld, ((N.Number - 1) * 4000) + 1, 4000)))

to list PKeys of unmatched ntext fields.

All the best.

David Catherman SSC Veteran Points: 281 More actions · Answer 7

I saw an example recently where they used the LIKE function to check for differences:

If T1.NTextFld LIKE T2.NTextFld

Begin

...Do something

Dave C

David P Reynevich Old Hand Points: 398 More actions · Answer 8

Hi Dave

I think that LIKE is subject to the the normal varchar (8000)/nvarchar (4000) restriction - also in a case-insensitive env I'm not sure that LIKE will differientiate between 'AA' and 'aa'.

Let me know if you think otherwise.

Cheers.

DR

PS I've changed the CHECKSUM to BINARY_CHECKSUM (in previous code) to get a case senstive comparison.