如何使用函数在SQL Server中添加逗号分隔值 [英] How to add comma seperated value in sql server using function

问题描述

在我的表中，我有一个字段，如数量，它将存储值，如1200、1500、2000、5000，我想将它们相加，结果将为9700.但是此结果不会存储在任何字段中，所以我想添加使用存储过程中的函数在运行时设置值.

In my table i have a field like amount which will store value like 1200,1500,2000,5000 and i want to add them and result will be 9700. but this result will not store in any field so i want to add value at run time using function in store procedure.

推荐答案

没有直接的方法可以做到这一点.但是，这可能会有所帮助:

There is no direct way to do that. However, this might help:

DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = '2,3,177,'
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ','
DECLARE @TOTAL INT
SET @TOTAL = 0
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   SET @TOTAL = @TOTAL + CONVERT(INT,@VALUE)
END
SELECT @TOTAL

是的，我们直接方法.尝试以下代码块

Yes we have direct way. Try the following code block

WITH XCTE AS
(
   SELECT  CAST('<i>' + REPLACE(ColumnName, ',', '</i><i>') + '</i>' AS XML) AS Vals
    FROM YourTable
)

SELECT SUM(x.i.value('.', 'INT')) AS Vals
FROM XCTE
CROSS APPLY Vals.nodes('//i') x(i)

注意:-您可以使用WHERE子句(如果需要)在CTE(公用表表达式)中过滤结果.

谢谢

Note:- You can filter the result in the CTE(Common Table Expression) by Using WHERE clause(If needed).

Thank You

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