Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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
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
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
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial