如何在SQL中加逗号分隔的字符串? [英] How to sum a comma separated string in SQL?

问题描述

id  value
1   1,2,3,4
2   2,3,4

所以我想得到这个结果:

So I want to get this result:

id   sum
1     10
2     9

我可以在SQL(MySQL)中做到吗?

Can I do it in SQL(MySQL)?

推荐答案

您可以更加动态地创建函数.请按照以下步骤操作

you can do it more dynamically Creating a function. Please follow the following steps

1. 创建一个给出逗号分隔值之和的函数

1. create a function that give the sum of a comma separated value

CREATE FUNCTION GetToalOfCommaSeperatedVal 
(
  @commaSeperatedVal varchar(100)
)
RETURNS int
AS
BEGIN

    declare @sum int
    DECLARE @x XML 
    SELECT @x = CAST('<A>'+ REPLACE(@commaSeperatedVal,',','</A><A>')+ '</A>' AS XML)

    SELECT @sum=sum(t.value('.', 'int')) 
      FROM @x.nodes('/A') AS x(t)

   return @sum
END
GO 

通过以下方式执行公正选择命令

the do a just select command in the following way

select id,dbo.GetToalOfCommaSeperatedVal(value) from YOUR_TABLE

这篇关于如何在SQL中加逗号分隔的字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！