Replace specific numbers in comma-separated list

Question

I'm trying to replace a combination of numbers using the REPLACE function in SQL Server but a specific ones inside a specific row. For example: row value = '17,171,217,317,28' and will do it like this:

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

but that will replace all the 17 found it in the column row.

How do I specify to only replace the starting 17 found in the row?

Answer 1

Try using this:

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

Note that I agree not to store your data like that, violates 1NF (First Normal Form)

Answer 2

If I understand the requirement correctly:

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

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

• Example db<>fiddle

Answer 3

If you can fix your database design to not use comma separated lists, that is the best solution. If that is not an option, the following approach may work.

I assume that you want to remove whole values at either the start, middle, or end of the list, such as "17,171,217,317,28", "13,17,19", or "11,13,17". If the same value exists more than once, all should be removed. Example: "17,23,17,217,17" would become "23,217".

This can be done by adding extra delimiters at the start/end, replacing the delimited value (delimiter + value + delimiter) with a single delimiter, and then stripping the extra delimiters just added. If there is a chance that duplicate consecutive values may exist, existing delimiters will need to be temporarily doubled up and the replace would change to simply remove any matching (delimiter + value + delimiter) sequance.

Logic to handle all of the edge cases can get complex. The following appears to be fairly robust over a variety of test data.

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

Results:

ValueList	UpdatedValueList
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

The TRIM(char FROM string) syntax is available in SQL Server 2019 and later. For older versions, trimming leading and trailing delimiters gets tricky. One approach involves a STUFF/REVERSE/STUFF/REVERSE sequence, with a special case needed to handle an empty result.

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

See this db<>fiddle for a demo.

Answer 4

If preserving order does not matter, you can use STRING_SPLIT() and STRING_AGG().

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

See this db<>fiddle fr a demo.

Caution. Although The examples tend to preserve order in small lists, this is not guaranteed in the function documentation. These functions are only available in SQL Server 2017 and later.