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.

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

Open in new window

Southern_GentlemanAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
I took a completely different approach, using code-generated grunt-level REPLACEs (to avoid recursion issues and just overall hassle).  This should perform fine until/unless you get a very long list of replacements (probably more than 100 items).  Even then, it might work as well as all but the most tuned code.

Note that I added one more replacement value in table1 for testing so that 2 strings would need replaced on the same line:
INSERT INTO #TEMPTABLE1 (ID,COL2) VALUES(8,'qui')


DECLARE @col2_likes varchar(max)
DECLARE @col2_replaces varchar(max)
DECLARE @exec_sql bit
DECLARE @print_sql bit
DECLARE @sql_template varchar(max)

SET @print_sql = 1
SET @exec_sql = 0 /* change to 1 to run SQL replaces rather than just see them */

SET @sql_template = '
UPDATE T2
SET COL2 = STUFF(REPLACE(CA2.COL2_PADDED,
    $col2_replaces$
    , 1, 2, '''')
FROM #TEMPTABLE2 T2
CROSS APPLY (
    SELECT '', '' + T2.COL2 AS COL2_PADDED
) AS CA2
WHERE $col2_likes$
'

SET @col2_replaces = ''
SET @col2_likes = ''
SELECT
    @col2_replaces = @col2_replaces + '''' + CA1.COL2_PADDED + ''', ''''),',
    @col2_likes = @col2_likes + 'OR CA2.COL2_PADDED LIKE ''%' + CA1.COL2_PADDED + '%'' ' + CHAR(13) + CHAR(10)
FROM #TEMPTABLE1 T1
CROSS APPLY (
    SELECT ', '  + T1.COL2 AS COL2_PADDED
) AS CA1

SET @col2_replaces = LEFT(@col2_replaces, LEN(@col2_replaces) - 1)
SET @col2_likes = STUFF(@col2_likes, 1, 3, '')

--SELECT @col2_replaces, @col2_likes

SET @sql_template = REPLACE(REPLACE(REPLACE(@sql_template,
    'REPLACE(', REPLICATE(CAST('REPLACE(' AS varchar(max)), (SELECT COUNT(*) FROM #TEMPTABLE1))),
    '$col2_replaces$', @col2_replaces),
    '$col2_likes$', @col2_likes)

IF @print_sql = 1
    PRINT @sql_template
IF @exec_sql = 1
BEGIN
    SELECT 'Before', * FROM #TEMPTABLE2
    EXEC(@sql_template)
    SELECT 'After', * FROM #TEMPTABLE2
END /*IF*/
0
 
Dustin SaundersDirector of OperationsCommented:
You could do an UPDATE with CROSS APPLY

UPDATE #TEMPTABLE2
SET COL2 = REPLACE(t2.COL2,', '+t1.COL2,'')
FROM #TEMPTABLE2 t2
CROSS APPLY #TEMPTABLE1 t1
WHERE PATINDEX('%'+t1.COL2+'%',t2.COL2) > 0;

Open in new window


Result:
1	xxx, bbb, ttt, sss
2	sst, dds, xfs, pps
3	xdt, lfu, ijd, yyt, ssy
4	spy, snm, scf, ser, uti
5	pwg, hio, ein, stu, ope
6	lui, dii, qui, mbk

Open in new window



Full example:
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

UPDATE #TEMPTABLE2
SET COL2 = REPLACE(t2.COL2,', '+t1.COL2,'')
FROM #TEMPTABLE2 t2
CROSS APPLY #TEMPTABLE1 t1
WHERE PATINDEX('%'+t1.COL2+'%',t2.COL2) > 0;

SELECT * FROM #TEMPTABLE2

DROP TABLE #TEMPTABLE1
DROP TABLE #TEMPTABLE2

Open in new window

0
 
Máté FarkasDatabase Developer and AdministratorCommented:
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

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Máté FarkasDatabase Developer and AdministratorCommented:
Dustin Saunders, your code does not remove xdt from line 3.
1
 
Dustin SaundersDirector of OperationsCommented:
Yes, I was missing a replace step there.  But it turns out we both have the same 2 problems:

Your result 2 shows
sst, dds, xfs, pps, 

Open in new window

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.
0
 
ina_donCommented:
Hi,

I'm not going to give you the whole answer however, I'll give you code that will help you complete your answer.

I created the tables so you might want to correct this and use temp tables as in your example but here is a select that will focus only on the rows where the replacement has to be done. You can just add the update clause as necessary:

SELECT
      t1.ID,
      CASE WHEN RIGHT(REPLACE(RTRIM(LTRIM(SUBSTRING(t2.col2, 0, PATINDEX('%' + t1.col2 + '%',t2.COL2)-1) + SUBSTRING(t2.col2, PATINDEX('%' + t1.col2 + '%',t2.COL2) + LEN (t1.COL2) + 1, LEN(t2.col2) - PATINDEX('%' + t1.col2 + '%',t2.COL2)))), ',,', ','),1) = ','
      THEN SUBSTRING(REPLACE(RTRIM(LTRIM(SUBSTRING(t2.col2, 0, PATINDEX('%' + t1.col2 + '%',t2.COL2)-1) + SUBSTRING(t2.col2, PATINDEX('%' + t1.col2 + '%',t2.COL2) + LEN (t1.COL2) + 1, LEN(t2.col2) - PATINDEX('%' + t1.col2 + '%',t2.COL2)))), ',,', ','),0,LEN(REPLACE(RTRIM(LTRIM(SUBSTRING(t2.col2, 0, PATINDEX('%' + t1.col2 + '%',t2.COL2)-1) + SUBSTRING(t2.col2, PATINDEX('%' + t1.col2 + '%',t2.COL2) + LEN (t1.COL2) + 1, LEN(t2.col2) - PATINDEX('%' + t1.col2 + '%',t2.COL2)))), ',,', ',')))
      ELSE
      REPLACE(
            RTRIM(
                  LTRIM(SUBSTRING(t2.col2, 0, PATINDEX('%' + t1.col2 + '%',t2.COL2)-1) + SUBSTRING(t2.col2, PATINDEX('%' + t1.col2 + '%',t2.COL2) + LEN (t1.COL2) + 1, LEN(t2.col2) - PATINDEX('%' + t1.col2 + '%',t2.COL2))
                        )
                  ), ',,', ','
            )
      END
AS FinalVal  

FROM TEMPTABLE1 t1 INNER JOIN TEMPTABLE2 t2 ON t1.ID = t2.ID
0
 
Mark WillsTopic AdvisorCommented:
Could there be differing lengths like 'pp' in table 1, or are they going to match ?

select t2.id, t2.col2 OLDCOL2,t1.id, t1.COL2 as REMOVECOL2, NEWCOL2 = STUFF(REPLACE(', '+t2.COL2,', '+t1.COL2,''),1,2,'')
FROM #TEMPTABLE2 t2
CROSS APPLY #TEMPTABLE1 t1
WHERE PATINDEX('%,'+t1.COL2+',%',replace(','+t2.COL2+',',' ','')) > 0;

Open in new window

If that works for you then
UPDATE t2
SET COL2 = STUFF(REPLACE(', '+t2.COL2,', '+t1.COL2,''),1,2,'')
FROM #TEMPTABLE2 t2
CROSS APPLY #TEMPTABLE1 t1
WHERE PATINDEX('%,'+t1.COL2+',%',replace(','+t2.COL2+',',' ','')) > 0  

Open in new window

BUT always test and backup (even select * into #backup_temptable2 from #temptable2) before you commit
0
 
Southern_GentlemanAuthor Commented:
thank you all for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.