Comparing two column strings for differences and updating table

given two tables with columns like so:
TableA
id Col1
1 blue,green,pink
2 fat,ugly,stupid,smart
3 larry,james,philip,john

TableB
id Col2
3  larry,james,tih,john,ann,,
2 fat,krank,ugly,stupid,smart
1 yellow

I want to be able to find and add (append) to TableB (col2) all the missing words found in TableA, Col1 using the id as the relationship that link both tables.  The end result should be Col1 a subset of Col2. words are comma seperated.

I found a function which was very handy in the comparism of strings but i could not figure how to implement it to update the table with missing values;

 using the DelimitedSplit8K function, found here
http://www.sqlservercentral.com/Forums/Topic1518039-8-1.aspx


DECLARE @String1 varchar(30) = '356,840',
        @String2 varchar(30) = '840,356'

IF NOT EXISTS(
            SELECT fn.Item from dbo.DelimitedSplit8K(@String1,',') fn
            EXCEPT
            SELECT fn.Item from dbo.DelimitedSplit8K(@String2,',') fn
            )
            PRINT 'No Differences found'
ELSE
            PRINT 'they are not the same, regardless of order'

--*******************************************************************
I ll like to put an update statement in the place of
   PRINT 'they are not the same, regardless of order'

update TableB
set Col2 = Col2 + Col1
where 'the camparism above shows a mismatch'

so the result of the select statement will be like this;
select col2 from TableB where id = 1

results;
blue,green,pink,yellow
peterslove53Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Try something like this...

DECLARE @TableA TABLE
(
	ID		INT IDENTITY(1,1) NOT NULL,
	Col1	VARCHAR(MAX) NOT NULL
);

DECLARE @TableB TABLE
(
	ID		INT IDENTITY(1,1) NOT NULL,
	Col2	VARCHAR(MAX) NOT NULL
);

INSERT INTO @TableA (Col1)
VALUES ('blue,green,pink'),
	('fat,ugly,stupid,smart'),
	('larry,james,philip,john');

INSERT INTO @TableB (Col2)
VALUES ('yellow'),
	('fat,krank,ugly,stupid,smart'),
	('larry,james,tih,john,ann')

--SELECT * FROM @TableA;
--SELECT * FROM @TableB;

DECLARE @id	INT = 1;

UPDATE @TableB
SET Col2 =
	STUFF(
	(
		SELECT ',' + Combined.Value
		FROM
		(
			SELECT A.ID, SplitA.Value
			FROM @TableA AS A
			CROSS APPLY Kepler.dbo.SplitText(A.Col1, ',') AS SplitA
			WHERE A.ID = @id
			UNION
			SELECT B.ID, SplitB.Value
			FROM @TableB AS B
			CROSS APPLY Kepler.dbo.SplitText(B.Col2, ',') AS SplitB
			WHERE B.ID = @id
		) AS Combined
		FOR XML PATH('')
	), 1, 1, '')
WHERE ID = @ID

SELECT * FROM @TableB

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OriNetworksCommented:
You should be able to do something similar to

UPDATE TableA
FROM 
TableA a inner join TableB b on a.ID=b.ID set a.Col1 = (
select item + ',' from
    (select distinct t1.item from
      (Select item from dbo.DelimitedSplit8K(a.Col1) UNION ALL Select item from dbo.DelimitedSplit8K(a.Col2) ) t1
    ) t2 FOR XML PATH ('')
)

Open in new window


This is saying update the field by joining the comma separated records from each field and get distinct values.
peterslove53Author Commented:
I get syntax error:
Incorrect syntax near '.'
at this row:
            CROSS APPLY Kepler.dbo.SplitText(A.Col1, ',') AS SplitA

on further investigation,, i read online that, this error may likely be as a result of;

database not running in Compatibility mode 90 or above
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

peterslove53Author Commented:
how can i just select all the IDs of the columns having a mismatch?
Brian CroweDatabase AdministratorCommented:
You would need to replace the SplitText function with one that you have available.

I'm not sure I understand what you're asking for in the latest comment.  Can you give an example?
peterslove53Author Commented:
i ll like to output the column Ids of table B which has a mismatch with tableA (i.e records of tableA where col1 in TableA  is not a subset of col2 in TableB. result wil be those with id (1 and 3) since they are not subsets of TableB
1
3
Brian CroweDatabase AdministratorCommented:
DECLARE @TableA TABLE
(
	ID		INT IDENTITY(1,1) NOT NULL,
	Col1	VARCHAR(MAX) NOT NULL
);

DECLARE @TableB TABLE
(
	ID		INT IDENTITY(1,1) NOT NULL,
	Col2	VARCHAR(MAX) NOT NULL
);

INSERT INTO @TableA (Col1)
VALUES ('blue,green,pink'),
	('fat,ugly,stupid,smart'),
	('larry,james,philip,john');

INSERT INTO @TableB (Col2)
VALUES ('yellow'),
	('fat,krank,ugly,stupid,smart'),
	('larry,james,tih,john,ann');

--SELECT * FROM @TableA;
--SELECT * FROM @TableB;

WITH cteTableA AS
(
	SELECT A.ID, SplitA.Value
	FROM @TableA AS A
	CROSS APPLY Kepler.dbo.SplitText(A.Col1, ',') AS SplitA
),
cteTableB AS
(
	SELECT B.ID, SplitB.Value
	FROM @TableB AS B
	CROSS APPLY Kepler.dbo.SplitText(B.Col2, ',') AS SplitB
)
SELECT ID
FROM
(
	SELECT ISNULL(A.ID, B.ID) AS ID,
		SUM(CASE WHEN A.Value IS NOT NULL AND B.Value IS NULL THEN 1 ELSE 0 END) AS AOnlyCount,
		SUM(CASE WHEN A.Value IS NULL AND B.Value IS NOT NULL THEN 1 ELSE 0 END) AS BOnlyCount,
		SUM(CASE WHEN A.Value IS NOT NULL AND B.Value IS NOT NULL THEN 1 ELSE 0 END) AS Matches
	FROM cteTableA AS A
	FULL OUTER JOIN cteTableB AS B
		ON A.ID = B.ID
		AND A.Value = B.Value
	GROUP BY ISNULL(A.ID, B.ID)
) AS Summary
WHERE AOnlyCount > 0	-- Not a subset

Open in new window

Scott PletcherSenior DBACommented:
On the last query, you don't need a FULL JOIN nor a separate outer query, just add HAVING to the main query:

cteTableB AS
)
    ...
)
SELECT A.ID,
            SUM(CASE WHEN A.Value IS NOT NULL AND B.Value IS NULL THEN 1 ELSE 0 END) AS AOnlyCount,
            SUM(CASE WHEN A.Value IS NOT NULL AND B.Value IS NOT NULL THEN 1 ELSE 0 END) AS Matches
FROM cteTableA AS A
LEFT OUTER JOIN cteTableB AS B
      ON A.ID = B.ID
      AND A.Value = B.Value
GROUP BY A.ID
HAVING SUM(CASE WHEN A.Value IS NOT NULL AND B.Value IS NULL THEN 1 ELSE 0 END) > 0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.