Home Remove value from comma delimited string SQL Server
Reply: 3

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
(
    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.

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#
Paparazzi Reply to 2018-01-10 19:53:12Z

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;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304029 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO