ntext comparison
-
Does anyone have a good solution to determining whether 2 ntext fields are the same?
Thanks.
-
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 -
Hi Noel
Comparison is on 2 tables - same field. Required functionality is:
if Table(prev).ntextField <> Table(curr).ntextField
process change
Thanks.
-
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 -
Wouldn't it be simpler to use the numbers table to do the splitting of the text fields??
-
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 -
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.
-
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
-
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply