I have a comma-separated value like 1,2,3,4
in one the column in a table in my SQL Server database. I want to replace a particular value in the comma separated string. i.e., from 1,2,3
I have to replace 1 with 5 and 2 with 6. The expected output is 5,6,3
.
我在SQL Server数据库的表中的一列中有一个逗号分隔值,如1,2,3,4。我想替换逗号分隔字符串中的特定值。即,从1,2,3我必须用5替换1和用6替换2.预期的输出是5,6,3。
I will have the value 1 and 2 in multiple rows. So I need to update it in all the rows. I have a table which contains the new value to be updated (i.e., 5 and 6
).
我将在多行中获得值1和2。所以我需要在所有行中更新它。我有一个表,其中包含要更新的新值(即5和6)。
In short, I have a table having comma separated values in one of the columns and I have another table which contains the new value. I need to update the comma separated value with the new value.
简而言之,我有一个表在其中一列中有逗号分隔值,我有另一个包含新值的表。我需要用新值更新逗号分隔值。
4 个解决方案
#1
2
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 `
#2
0
you can move your comma seperated values from your row into a new table with a fetch and a function like this;
你可以将行中的逗号分隔值移动到一个带有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
then you can update however you like to update them.
然后你可以更新但是你想要更新它们。
i d keep that table and drop the column.. but you may update them back to your table if its infrastructural or its too late to change.
我保留那张桌子并放下栏目..但是如果它的基础设施或者为时已晚,你可以将它们更新回你的桌子。
#3
0
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
#4
-1
You can use query like
您可以使用查询
select REPLACE(REPLACE([Value], 1, 5), 2, 6)
from TestTable
and insert into newer table column.
并插入较新的表列。
But still it is depends on your requirement.
但这仍然取决于您的要求。
Thanks.
#1
2
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 `
#2
0
you can move your comma seperated values from your row into a new table with a fetch and a function like this;
你可以将行中的逗号分隔值移动到一个带有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
then you can update however you like to update them.
然后你可以更新但是你想要更新它们。
i d keep that table and drop the column.. but you may update them back to your table if its infrastructural or its too late to change.
我保留那张桌子并放下栏目..但是如果它的基础设施或者为时已晚,你可以将它们更新回你的桌子。
#3
0
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
#4
-1
You can use query like
您可以使用查询
select REPLACE(REPLACE([Value], 1, 5), 2, 6)
from TestTable
and insert into newer table column.
并插入较新的表列。
But still it is depends on your requirement.
但这仍然取决于您的要求。
Thanks.