CODEWITHSUNDEEP
sqlsql-servert-sql
Tony
Tony

Reputation: 149

Remove value from comma delimited string SQL Server

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.

Upvotes: 2

Views: 10900

Answers (4)

Tomasz
Tomasz

Reputation: 11

If you are using SQL 2017 you can use the STRING_AGG function:

declare @valueToRemove varchar(2) = '15'

DECLARE @T AS TABLE (id int not null identity, BadColumn varchar(100))

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

; with result as ( select id as orgId, STRING_AGG(result.res, ',') as newVal from ( select id, value as res from @T cross apply string_split(BadColumn, ',') where trim(value) != @valueToRemove ) result group by id)

update @T set BadColumn = newVal from result where id = orgId

update @t set BadColumn = '' where trim(BadColumn) = @valueToRemove -- you wouldnt need this if the update was id specific

select * from @T

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

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;

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

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
(
    BadColumn varchar(100)
)

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, '')
        , '') As StillBadButWithout15
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
SET BadColumn = ISNULL(
                    STUFF(
                        STUFF(Without15, 1, 2, ''), 
                        LEN(Without15)-2, 2, '')
                , '')

Here is the live demo for this.

Upvotes: 4

Alan Burstein
Alan Burstein

Reputation: 7918

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);

Upvotes: 0

Related Questions