替换逗号分隔列表中的特定数字

Question

我正在尝试使用 SQL Server 中的 REPLACE 函数替换数字组合，但替换特定行中的特定数字组合。例如： row value = '17,171,217,317,28' 并将这样做：

UPDATE TABLENAME SET COLUMN_NAME = REPLACE(column value,'17,','')

但这将替换在列行中找到的所有 17 个。

如何指定仅替换行中找到的开头 17 个？

Answer 1

尝试使用这个：

UPDATE TABLENAME SET COLUMN_NAME = REPLACE(','+column value,',17,','')

请注意，我同意不这样存储您的数据，违反了 1NF（第一范式）

Answer 2

如果我正确理解了要求：

DECLARE @ReplaceValue varchar(32) = '17,';

UPDATE dbo.TABLENAME 
  SET COLUMN_NAME = STUFF(COLUMN_NAME, 1, LEN(@ReplaceValue), '')
  WHERE COLUMN_NAME LIKE @ReplaceValue + '%';

• 示例 db<>fiddle

Answer 3

如果您可以修复数据库设计以不使用逗号分隔列表，那么这是最好的解决方案。如果这不是一个选择，则以下方法可能有效。

我假设您想要删除列表开头、中间或结尾处的整个值，例如“17,171,217,317,28”、“13,17,19”或“11,13,17”。如果相同的值多次存在，则应将其全部删除。示例：“17,23,17,217,17”将变为“23,217”。

这可以通过在开始/结束处添加额外的分隔符、用单个分隔符替换分隔值（分隔符 + 值 + 分隔符），然后删除刚刚添加的额外分隔符来完成。如果可能存在重复的连续值，则现有分隔符将需要暂时加倍，并且替换将更改为仅删除任何匹配的（分隔符 + 值 + 分隔符）序列。

处理所有边缘情况的逻辑可能会变得复杂。以下内容对于各种测试数据似乎相当稳健。

DECLARE @ValueToRemove VARCHAR(100) = '17'
DECLARE @Delim VARCHAR(10) = ','

UPDATE @Data
SET UpdatedValueList =
    TRIM(@Delim FROM
        REPLACE(REPLACE(REPLACE(
            @Delim + ValueList + @Delim  -- Add leading and trailing delimiters
            , @Delim, @Delim + @Delim)  -- Double up delimiters
            , @Delim + @ValueToRemove + @Delim, '')  -- Remove value
            , @Delim + @Delim, @Delim)  -- Undo doubled delimiters
        )  -- Trim leading and trailing delimiters

结果：

值列表	更新值
列表 17,171,217,317,28	171,217,317,28
13,17,19	13,19
11,13,17	11,13
17,23,17,217,17	23,217
17,18	18
16,17	16
16,17,17,18	16,18
17
16	16

TRIM(char FROM string) 语法在 SQL Server 2019 及更高版本中可用。对于旧版本，修剪前导和尾随分隔符会变得很棘手。一种方法涉及 STUFF/REVERSE/STUFF/REVERSE 序列，需要处理空结果的特殊情况。

UPDATE @Data
SET UpdatedValueList =
    ISNULL(REVERSE(STUFF(REVERSE(STUFF(
        REPLACE(REPLACE(REPLACE(
            @Delim + ValueList + @Delim  -- Add leading and trailing delimiters
            , @Delim, @Delim + @Delim)  -- Double up delimiters
            , @Delim + @ValueToRemove + @Delim, '')  -- Remove value
            , @Delim + @Delim, @Delim)  -- Undo doubled delimiters
        , 1, LEN(@Delim), '')) -- Trim leading delimiter and reverse
        , 1, LEN(@Delim), '')) -- Trim trailing delimiter and undo reverse
        , '') -- Special case for empty result (when prior STUFF yielded null)
SELECT * FROM @Data

请参阅此 db<>fiddle 进行演示。

Answer 4

如果保留顺序并不重要，则可以使用 STRING_SPLIT() 和 STRING_AGG()。

UPDATE @Data 
SET UpdatedValueList = ISNULL((
    SELECT STRING_AGG(S.Value, ',')
    FROM STRING_SPLIT(ValueList, ',') S
    WHERE S.Value <> @ValueToRemove
    ), '')

请参阅此 db<>fiddle 演示。

警告。尽管示例倾向于保留小列表中的顺序，但函数文档中并不能保证这一点。这些函数仅在 SQL Server 2017 及更高版本中可用。