如何在 SQL Server 中替换逗号分隔字符串列中的值

Question

我有一个逗号分隔的值，例如1,2,3,4在我的SQL Server数据库中表的一个列中。我想替换逗号分隔字符串中的特定值。即，从1,2,3我必须用 1 替换 5，用 2 替换 6。预期输出为5,6,3。

我将在多行中具有值 1 和 2。所以我需要在所有行中更新它。我有一个包含要更新的新值(即5 and 6(的表。

简而言之，我有一个表，其中一列中有逗号分隔的值，我有另一个包含新值的表。我需要用新值更新逗号分隔的值。

Answer 1

WITH cte  AS (SELECT A.*,
T.pkcolumn, 
T.column1 
FROM   table1 AS T 
CROSS apply String_split(column1, ',') AS A 
WHERE  column1 = '<oldValue>' 
OR column1 LIKE '<oldValue>,%' 
OR column1 LIKE '%,<oldValue>,%' 
OR column1 LIKE '%,<oldValue>') 
UPDATE Y 
SET    column1 = Stuff((SELECT ',' + CASE WHEN value = '<oldValue>' THEN 
'<newValue>' 
ELSE value 
END 
FROM   cte t1 
WHERE  t1.pkcolumn = t2.pkcolumn 
FOR xml path ('')), 1, 1, '') 
FROM   cte t2 
LEFT OUTER JOIN table1 AS Y 
ON Y.pkcolumn = t2.pkcolumn `

Answer 2

您可以使用 fetch 和这样的函数将逗号分隔的值从行移动到新表中;

CREATE FUNCTION [dbo].[SplitToItems]
(
@pString    NVARCHAR(3999), --!! DO NOT USE MAX DATA-TYPES
@pDelimiter CHAR(1)
)
RETURNS @Items TABLE
(
ItemNumber Integer,
Item nvarChar(100)
)
BEGIN
if Replace(@pString,'''','') = ''
set @pString=''
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),                          --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
INSERT INTO @Items
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item       = SUBSTRING(SUBSTRING(@pString, l.N1, l.L1),1,100)
FROM cteLen l
RETURN
END

然后，您可以根据需要进行更新。

我会保留该表并删除该列..但是如果其基础结构或更改太晚，您可以将它们更新回您的表。

Answer 3

declare @str varchar(20) = '1,2,3,4,5,6'
select REPLACE(replace(@str, '1', '5'), '2', '6')
--replace 1 with 5 and 2 with 6

Answer 4

您可以使用类似查询

select REPLACE(REPLACE([Value], 1, 5), 2, 6) 
from TestTable

并插入到较新的表列中。

但这仍然取决于您的要求。

谢谢。