如何在SQL Server中使用逗号和数字？

Z时代
2024-01-10
分类：问答

我有像（1100,2014,4000）的字符串字段我想分离逗号和总结每个数字彼此例如： 1100,2014,4000如何在SQL Server中使用逗号和数字？

1 1 0 0 2 0 1 4 4 0 0 0

结果

@first =1+2+4=7 
@second= 1+0+0=1 
@third=0+1+0=1 
@forth=0+4+0=4

回答：

试试这个，

DECLARE @Table TABLE(Value VARCHAR(20)) 
INSERT INTO @Table VALUES('1100,2014,4000') 
DECLARE @First INT, @Second INT, @Third INT, @Fourth INT 
SELECT Split.a.value('.', 'VARCHAR(100)') AS Data 
INTO #temp 
FROM 
(
    SELECT CAST ('<M>' + REPLACE(Value, ',', '</M><M>') + '</M>' AS XML) AS Value 
    FROM @Table 
) AS A CROSS APPLY Value.nodes ('/M') AS Split(a); 
SELECT @First=SUM(Data/1000) 
    ,@Second=SUM((Data%1000)/100) 
    ,@Third=SUM((Data%100)/10) 
    ,@Fourth=SUM((Data%10)) 
FROM #temp 
SELECT @First, @Second, @Third, @Fourth 
DROP TABLE #temp

回答：

这可能有助于获得值之和是这样的：

SELECT SUM(CAST(SUBSTRING(X.A, 1, 1) AS INT)) first, 
    SUM(CAST(SUBSTRING(X.A, 2, 1) AS INT)) second, 
    SUM(CAST(SUBSTRING(X.A, 3, 1) AS INT)) third, 
    SUM(CAST(SUBSTRING(X.A, 4, 1) AS INT)) fourth 
FROM (SELECT '1100' A 
     UNION 
     SELECT '2014' A 
     UNION 
     SELECT '4000' A 
    ) X

回答：

为此，首先，采用杰夫MODEN的DelimitedSplit8K（因为我不是k现在是什么版本的SQl服务器）。其次，你的逻辑看起来有点过分。你说变量@third的值是0+4+0，但是，这是整数的第4个字符。另外，这个假设所有整数是4个字符长。

WITH VTE AS(
    SELECT * 
    FROM (VALUES('1100,2014,4000')) v(DSn)) --This is your samnple data 
SELECT SUM(CONVERT(int,SUBSTRING(RIGHT('0000' + DS.Item,4),N.I, 1))) AS [Sum] 
FROM VTE 
    CROSS APPLY dbo.DelimitedSplit8K (VTE.DSn,',') DS 
    CROSS APPLY (VALUES (1),(2),(3),(4)) N(I) 
GROUP BY N.I;

回答：

您可以使用此函数将所有项目排在表格中。

您可以创建此功能，并尝试再 SELECT * FROM dbo.string2table（ '1243,1234,2343'， ''）

CREATE FUNCTION [dbo].[string2table] 
(
    @string VARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(
    data VARCHAR(256) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
     IF @end = 0 
      SET @end = LEN(@string) + 1 
     INSERT INTO @output (data) 
     VALUES(SUBSTRING(@string, @start, @end - @start)) 
     SET @start = @end + 1 
     SET @end = CHARINDEX(@delimiter, @string, @start) 
    END 
    RETURN 
END

回答：

继具有多个SQL CTE表达式的Select语句可以在numbers table function和SQL split string function的帮助下用于使用“，”分隔数字列表，作为分隔符

对于进一步分割各数到它的标号，我更愿意使用子串函数

with cte as (
    select 
    * 
    from NumbersList n, 
    dbo.NumbersTable(1,4,1) as nt 
), splitted as (
select 
    list, 
    id, 
    i, 
    substring(val,i,1) val 
from cte 
cross apply dbo.Split(list,',') v 
) 
select 
    distinct 
    list, 
    i, 
    sum(cast(val as int)) over (partition by list, i) sumOf 
from splitted