Southern_Gentleman
asked on
remove parts of csv string.
I'm trying to remove designated parts of a comma separated value string in my table.
SO my #temptable1 has the values that need to be removed in my #tempTable2, as well as keep the format of all the commas before and after each value in my csv string.
SO my #temptable1 has the values that need to be removed in my #tempTable2, as well as keep the format of all the commas before and after each value in my csv string.
CREATE TABLE #TEMPTABLE1(ID INT, COL2 VARCHAR(MAX))
CREATE TABLE #TEMPTABLE2(ID INT, COL2 VARCHAR(MAX))
INSERT INTO #TEMPTABLE1 (ID,COL2) VALUES(1,'ppp')
INSERT INTO #TEMPTABLE1 (ID,COL2) VALUES(2,'kky')
INSERT INTO #TEMPTABLE1 (ID,COL2) VALUES(3,'xdt')
INSERT INTO #TEMPTABLE1 (ID,COL2) VALUES(6,'dfi')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(1,'xxx, ppp, bbb, ttt, sss')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(2,'sst, dds, xfs, pps, kky')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(3,'xdt, lfu, ijd, yyt, ssy')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(4,'spy, snm, scf, ser, uti')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(5,'pwg, hio, ein, stu, ope')
INSERT INTO #TEMPTABLE2 (ID,COL2) VALUES(6,'lui, dii, qui, dfi, mbk')
SELECT * FROM #TEMPTABLE1
SELECT * FROM #TEMPTABLE2
--this is going to be final results of #tempTable2.
ID COL2
1 xxx, bbb, ttt, sss
2 sst, dds, xfs, pps
3 lfu, ijd, yyt, ssy
4 spy, snm, scf, ser, uti
5 pwg, hio, ein, stu, ope
6 lui, dii, qui, mbk
Use this query:
SELECT T2.ID, REPLACE(REPLACE(T2.COL2, ISNULL(T1.COL2 + ', ', ''), ''), ISNULL(T1.COL2, ''), '')
FROM #TEMPTABLE2 T2
LEFT JOIN #TEMPTABLE1 T1 ON T1.ID = T2.ID
Dustin Saunders, your code does not remove xdt from line 3.
Yes, I was missing a replace step there. But it turns out we both have the same 2 problems:
Your result 2 shows
Neither are recursive.
Can be fixed with a CTE expression which I'll put together in a bit when I get a moment.
Your result 2 shows
sst, dds, xfs, pps,
with trailing comma.Neither are recursive.
Can be fixed with a CTE expression which I'll put together in a bit when I get a moment.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you all for the help.
Open in new window
Result:
Open in new window
Full example:
Open in new window