SQL文本中的和数

Question

这是代码

select 'QWXWX/50,GNTGHNT/5,F/500'  

我想看看这些课文中数字的总和。我的预期结果是555。

Answer 1

你不应该在t-SQL中这样做，但无论如何，如果你想的话，你仍然可以：

with basedata (data) as 
(
select * from string_split('QWXWX/50,GNTGHNT/5,F/500',',')
)
select sum(cast(replace(data, rtrim(translate(data, '0123456789',space(10))), '') as int))  from basedata;

DBFiddle演示

Answer 2

一种不同的方法，使用计数将非数字字符和逗号替换为零，然后拆分字符串并聚合它。我在这里包括nvarchar和varchar模式拆分器，但在解决方案中使用varchar模式拆分器：

CREATE OR ALTER FUNCTION dbo.PatternNCharacterReplace (@String nvarchar(4000), @Pattern nvarchar(100), @ReplacementCharacter nvarchar(1)) 
RETURNS table
AS RETURN
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT TOP(LEN(@String))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3, N N4)  --4096 rows; For a varchar(8000) or MAX you would need more rows for such lengths
SELECT STRING_AGG(CASE WHEN V.C LIKE @Pattern THEN @ReplacementCharacter ELSE V.C END,'') WITHIN GROUP (ORDER BY T.I) AS ReplacedString
FROM Tally T
CROSS APPLY (VALUES(SUBSTRING(@String,T.I,1)))V(C);
GO

CREATE OR ALTER FUNCTION dbo.PatternCharacterReplace (@String varchar(8000), @Pattern varchar(100), @ReplacementCharacter varchar(1)) 
RETURNS table
AS RETURN
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT TOP(LEN(@String))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3, N N4)  --10000 rows;
SELECT STRING_AGG(CASE WHEN V.C LIKE @Pattern THEN @ReplacementCharacter ELSE V.C END,'') WITHIN GROUP (ORDER BY T.I) AS ReplacedString
FROM Tally T
CROSS APPLY (VALUES(SUBSTRING(@String,T.I,1)))V(C);
GO
SELECT SUM(TRY_CONVERT(int, SS.[value]))
FROM dbo.YourTable YT
CROSS APPLY dbo.PatternCharacterReplace(YT.YourString, '[^0-9,]', '') PCR
CROSS APPLY STRING_SPLIT(PCR.ReplacedString, ',') SS
GROUP BY YT.ID; --Grouping in case you have multiple values. This may well be against a scalar value, so this wouldn't be needed

有趣的是，这似乎比Cetin的TRIM:db<gt；小提琴