从逗号分隔的字符串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:

无论位置如何，我都需要从拥有它的所有值中删除“15”。如您所见，数字可以是字符串开头，中间和最后的唯一值。完成后，我希望我的记录看起来像这样：

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.

我知道我可以创建3-4个单独的查询来处理所有不同的情况，但是真的想要有1个查询。

TIA, -TS.

TIA，-TS。

3 个解决方案

#1

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

如果您完全确定无法修复这个可怕的数据库设计，那么您可以使用CTE，REPLACE和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.

然后，CTE：注意我在实际值之前和之后添加了分隔符。

;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来显示结果，但你也可以用它来更新）

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.

观看rextester的现场演示。

Update

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

您可以将原始列添加到cte，只需直接更新cte：

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

这是现场演示。

#2

Similar to what Zohar posted - I have:

与Zohar发布的相似 - 我有：

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

#3

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;