如何在SQL Server中使用逗号和数字?
我有像(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;
我错过了@third = 1,@ forth = 4 感谢您的回答 和是的所有他们有4个字符 –
您可以使用此函数将所有项目排在表格中。
您可以创建此功能,并尝试再 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
我建议不要使用'CURSOR'。它们天生就很慢。 – Larnu
好的Larnu,我同意你的看法,特别是我不使用游标等待死锁,但是这个函数已经写好了,我要求解决这个问题。谢谢。 –
继具有多个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
输出为以下项
insert into NumbersList select '1100,2014,4000'
insert into NumbersList select '1111,2222,3456'
是如下
写一个函数,其可以返回每个数字的总和。 –
为什么要存储这样的数据? – Mureinik
你的桌子是怎样的? '1100,2014,4000'是一个单独的字符串或3个单独的行 – Squirrel