Home Remove value from comma delimited string SQL Server

# Remove value from comma delimited string SQL Server

Tony
1#
Tony Published in 2018-01-10 17:40:37Z
 Suppose I have a comma delimited string (I know, it's better to normalize it, but it's not an option) and the records look like this (note the space after each comma): 11, 15, 17, 23 15, 34, 46, 69 15 27, 41, 29, 15  I need to remove '15' from all of the values that have it regardless of the position. As you can see, the number can be the only value, in the beginning, middle, at the very end of the string. After I'm done, I would like my records to look like this: 11, 17, 23 34, 46, 69 27, 41, 29  I know I can create 3-4 separate queries to handle all of the different cases, but would really like to have 1 query. TIA, -TS.
Zohar Peled
2#
Zohar Peled Reply to 2018-01-10 19:02:51Z

If you are absolutely sure it's impossible to fix this terrible database design, here is something you can do, using a CTE, REPLACE, and STUFF

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
)

INSERT INTO @T VALUES
('11, 15, 17, 23'),
('15, 34, 46, 69'),
('15'),
('27, 41, 29, 15')


Then, the CTE: Note I'm adding the delimiter before and after the actual value.

;WITH CTE AS
(
SELECT REPLACE(', '+ BadColumn +', ', ', 15, ', ', ') As Without15
FROM @T
)


The query: (I've used select to show the results but you can use it to update as well)

SELECT ISNULL(
STUFF(
STUFF(Without15, 1, 2, ''),
LEN(Without15)-2, 2, '')
FROM CTE


Results:

StillBadButWithout15
11, 17, 23
34, 46, 69

27, 41, 29


See a live demo on rextester.

## Update

You can add the original column to the cte and simply update the cte directly:

;WITH CTE AS
(
SELECT BadColumn, REPLACE(', '+ BadColumn +', ', ', 15, ', ', ') As Without15
FROM @T
)

UPDATE CTE
STUFF(
STUFF(Without15, 1, 2, ''),
LEN(Without15)-2, 2, '')
, '')


Here is the live demo for this.

Alan Burstein
3#
Alan Burstein Reply to 2018-01-10 18:59:07Z
 Similar to what Zohar posted - I have: select BadColumn, GoodColumn = case when BadColumn like '15,%' then substring(BadColumn,4,8000) when BadColumn like '%,15,%' then replace(BadColumn,',15','') when badColumn like '%,15' then substring(BadColumn,1,datalength(badColumn)-3) when badColumn = '15' then '' else BadColumn end from (select BadColumn=rtrim(ltrim(replace(badColumn,' ',''))) from @t) map;  A splitter is overkill for this but here's what that solution would look like: with cleanT as (select BadColumn = replace(badColumn,' ','') from @T) select t.badColumn , cleanString = isnull(stuff(stringAgg.item,1,1,''),'') from cleanT t cross apply ( select ','+[value] from cleanT tx cross apply string_split(t.BadColumn,',') where t.BadColumn = tx.BadColumn and [value] <> '15' for xml path('') ) stringAgg(item); 
Paparazzi
4#
 Three simple updates and I suspect more efficient than a single update update table set col = REPLACE(col, ', 15', ''); update table set col = REPLACE(col, '15, ', ''); update table set col = null where col = 15;