Compare 2 Columns

[mamartin]

I have a table that contains remarks regarding a customer. The remarks can be up to 8000 characters in length and there can be multiple records for a given customer. What I need to do is compare the differences between 2 sets of remarks for a given customer and then list out what was changed from the first to the second. Example:

Customer A, Remark 1: this is an initial add of remarks.
Customer A, Remark 2: this is an initail add of remarks. Updated added on 03NOV10.

The process should return "Updated added on 03NOV10.". What makes things a bit more difficult is that the remark changes can be anywhere, not just appended to the end of the original remarks. I'm trying to do this in SQL Server 2000, but am willing to work with VB .NET or perhaps Excel. Any ideas?

 

[RiverGuy]

This could get you started. You might have to tweak this a bit.

DECLARE @test TABLE (
CustID CHAR(1),
RemarkNumber INT,
RemarkValue VARCHAR(8000))

INSERT INTO @test SELECT 'A', 1, 'this is an initial add of remarks.'
INSERT INTO @test SELECT 'A', 2, 'this is an initial add of remarks.  Updated added on 03NOV10.'
INSERT INTO @test SELECT 'A', 3, 'this is an initial add of remarks.  Updated added on 03NOV10.  Updated again.'
INSERT INTO @test SELECT 'B', 1, 'remark'
INSERT INTO @test SELECT 'B', 2, 'remark remark remark'
INSERT INTO @test SELECT 'C', 1, 'no change'
INSERT INTO @test SELECT 'C', 2, 'no change'
INSERT INTO @test SELECT 'D', 1, 'beginning'
INSERT INTO @test SELECT 'D', 2, 'change to beginning'
INSERT INTO @test SELECT 'E', 1, 'test2'
INSERT INTO @test SELECT 'E', 2, 'test1 test2 test3'





SELECT a.*
,ISNULL(LEFT(a.RemarkValue, NULLIF(PATINDEX('%' + b.RemarkValue + '%'  , a.RemarkValue) , 0) - 1), '')
+
SUBSTRING(a.RemarkValue, PATINDEX('%' + b.RemarkValue + '%'  , a.RemarkValue) + LEN(b.RemarkValue), 8000)
AS Change
FROM @test a
LEFT OUTER JOIN @test b
	ON b.RemarkNumber = a.RemarkNumber - 1
	AND a.CustID = b.CustID

 

[mamartin]

RiverGuy,

thanks for the prompt response and it does look promising. I'll do some tweaking and let you know.
Thanks.