Remove items in csv string

I was able to use the following query from Jim Horn Single column to normalize a comma string into a single column but then I did a NOT EXISTS from another table to find out what values need to be removed.

So now that i have that that information, how would i remove the items that i've found and go to the original CSV string and remove the one item or items that should not belong.

So, i have to figure out how to remove all the possible combinations of commas as well as well as the invalid item.
Southern_GentlemanAsked:
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Provide us sample data so we know what do deal with.
0
Southern_GentlemanAuthor Commented:
ok, here is a sample of what we are working with.

Table 1

customers      |      products
1                    |      aaa,bbb,ccc
2                    |      ddd,eee,fff
3                    |      bbb,ccc,ggg
4                    |      ppp,sss,ttt
5                     |      fff,qqq,mmm
6                    |      ooo,nnn,ddd


Table 2 (NOT VALID ITEMS LIST Or NOT EXISTS Results (insert into a temp table))

Customers      |      Products
1                    |      bbb
3                    |      ccc
4                    |      ppp


--Query to do the following

Results should be deleting the CSV value from table 2 products from the products in table1 since they are no longer valid
customers      |      products
1                    |      aaa,ccc
2                    |      ddd,eee,fff
3                    |      bbb,ggg
4                    |      sss,ttt
5                    |      fff,qqq,mmm
6                    |      ooo,nnn,ddd

So you can see that we removed 'bbb' for customer 1 and 'ccc' for customer 3  and 'ppp' for customer 4 from the actual csv string.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
My suggestion is to use the following solution:
UPDATE t1
SET t1.products = REPLACE(t1.products,t2.products,'')
FROM table1 t1
	INNER JOIN table2 t2 ON t1.customers = t2.customers

Open in new window

This will remove those products in table1 but will let double commas (when removed from the middle of the string), initial commas (where removing from the beginning of the string and final commas (when removing from the end of the string) so you'll need to run the following update to sort out this situation:
UPDATE table1
SET products = CASE
		WHEN LEFT(products,1)=',' THEN RIGHT(products,LEN(products)-1)
		WHEN RIGHT(products,1)=',' THEN LEFT(products,LEN(products)-1)
		ELSE REPLACE(products,',,',',')
	END

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PletcherSenior DBACommented:
Careful, that could destroy your data!
0
Southern_GentlemanAuthor Commented:
Thanks guys, i'm still testing this. the only thing i forgot was that the original string had spaces after the commas so the case when is missing some when statements

customers      |      products
1                    |      aaa, bbb, ccc
2                    |      ddd, eee, fff
3                    |      bbb, ccc, ggg
4                    |      ppp, sss, ttt
5                     |      fff, qqq, mmm
6                    |      ooo, nnn, ddd
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You just need to add the space in the REPLACE function in the ELSE statement and consider 2 characters for the other situations:
UPDATE table1
SET products = CASE
	WHEN LEFT(products,2)=', ' THEN RIGHT(products,LEN(products)-2)
	WHEN RIGHT(products,2)=' ,' THEN LEFT(products,LEN(products)-2)
	ELSE REPLACE(products,', ,',',')
	END

Open in new window

NOTE: Do not run this in your Production environment before testing it in a Development environment.
0
Southern_GentlemanAuthor Commented:
so i'm still analyzing this and for some reason in my live table it will remove the first results from my update query but won't update the other items that meet my criteria from the temp table.

UPDATE dbo.livetable
SET product = REPLACE(t1.product,t2.product,'')
FROM dbo.livetable as t1
	INNER JOIN #update t2 ON t1.customers = t2.customers AND t1.location = t2.location AND t1.programId = t2.programid

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Use the alias "t1" for the UPDATE:
UPDATE t1
SET t1.product = REPLACE(t1.product,t2.product,'')
FROM dbo.livetable as t1
	INNER JOIN #update t2 ON t1.customers = t2.customers AND t1.location = t2.location AND t1.programId = t2.programid

Open in new window

0

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
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.