When NTEXT or TEXT data is longer than allowed for varchar(max), comparing or replacing data in the string is difficult. This is made easy by use of Substring and Datalength functions in TSQL.
Below is a sample script where I had to load delta of note data. To do this I had to understand which notes were actually changed in the note field not just viewed or accessed by a user. Initial Data Load notes are in the _IDL table and delta is in the base table. If the replace returned empty or null there was not a change, otherwise the resulting note was different and had to replace the previously loaded note record.
The below is what was used to analyze the delta.
select T1.Id, T1.AccountId,T1.OpptyId,T1.ContactId,T1.CustomerServiceId,T1.ProjectId,T1.Subject,T1.Description
from T_SOD_STG_Notes T1, T_SOD_STG_Notes_IDL T2 WHERE T1.Id = T2.Id
AND Replace(SUBSTRING(T1.Description,1,DATALENGTH(T1.Description)),SUBSTRING(T2.Description,1,Datalength(T2.Description)),'') IS NOT NULL
AND Replace(SUBSTRING(T1.Description,1,DATALENGTH(T1.Description)),SUBSTRING(T2.Description,1,Datalength(T2.Description)),'') <> ''
Of course, I could put the exact same replace(substring(...,Datalength(...))) logic into the select and just selected the delta note as well.
Don't tell anyone, but I heard blogging is making a comeback!
