How to avoid duplication when replacing a specific value in a comma separated list?

Question

I have written a query that updates a certain value in a comma separated list to some other value. I do know it is a very bad database design to have comma separated lists but in this case i don't have any other option.

so the query is

SELECT * FROM AppUser 
WHERE AppValue LIKE '%429%' AND AppCode = 'BI' AND AppName = 'Categories' 
UPDATE AppUser
SET AppValue = REPLACE(AppValue, '429', '506')

Now the query above does replace 429 with 506, but what if there is already a 506 there. If I query it like that, it will result in duplication, and I will have two 506 like 405,506,565,506 instead of 405,506,565. So can you please guide me on how i would be able to avoid duplication?

Don't use `LIKE` to match the number. It will match `1429` and `4293` as well. Use `FIND_IN_SET()`. — Barmar, Dec 05 '18 at 20:53
Comma separated lists are evil. Restructure your [list of doom](http://gtowey.blogspot.com/2009/12/how-to-fix-comma-separated-list-of-doom.html) and you won't have as many query problems. — danblack, Dec 05 '18 at 20:54
@MatBailie He understands that it's bad design, read the question. — Barmar, Dec 05 '18 at 20:55
`SSMS` is `SQL Server Management Studio`, which isn't normally used with `MySQL`. Is this `SQL Server` or is it `MySQL`? — MatBailie, Dec 05 '18 at 21:02

MatBailie · Accepted Answer · 2018-12-05T21:22:10.083

You have another problem with your approach; REPLACE('123,456,789', '45', 'anything') will replace when you don't want to.

YOu also tagged this SSMS (SQL Server) as well as MySQL, so I'm just going to guess SQL Server...

UPDATE
  test
SET
  appValue = TRIM(',' FROM REPLACE(source.value, ',406,', replacement.value))
FROM
  test
CROSS APPLY
(
  SELECT ',' + appValue + ','
)
  source(value)
CROSS APPLY
(
  SELECT CASE WHEN source.value LIKE '%,506,%' THEN ',' ELSE ',506,' END
)
  replacement(value)
WHERE
  source.value LIKE '%,406,%'

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=010d5fb74fbc5c46c201cb13ac753f39

Or, a more generalised version...

UPDATE
  test
SET
  appValue = TRIM(',' FROM REPLACE(inputs.expression, inputs.pattern, replacement.value))
FROM
  test
CROSS APPLY
(
  SELECT
    ',' + appValue + ','   AS expression,
    ',' + '406'    + ','   AS pattern,
    ',' + '506'    + ','   AS replacement
)
  inputs
CROSS APPLY
(
  SELECT
    CASE WHEN inputs.expression LIKE '%' + inputs.replacement + '%'
         THEN ','
         ELSE inputs.replacement
     END
)
  replacement(value)
WHERE
  inputs.expression LIKE '%' + inputs.pattern + '%'

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9eb71e1e90b07c8c150004dc9a6d5107

I am using sql server 2014. Do you know how i would be able to use this query in sql server 2014 environment? Your query only works in Sql server 2017. I would appreciate if you can help me with it. Thank you — Manish Shiwlani, Dec 13 '18 at 15:24

score 0 · Answer 2 · answered Dec 05 '18 at 22:16

I'd create a stored procedure that creates a temporary table to allow for normalisation, populate the table from the comma-separated values, do whatever changes you wish, then update the field holding the comma-separated values from the normalised temporary table.

How to avoid duplication when replacing a specific value in a comma separated list?

2 Answers2