SQL sum numbers in text-CodePudding

This is the code

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

I want to see sum of the numbers in this texts. My expectation result is 555.

CodePudding user response：

You shouldn't do this in T-SQL, but anyway you still could if you wanted to:

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 demo

CodePudding user response：

A different approach using a tally to replace the non-numerical characters and comma with nothing, and then split the string and aggregate it. I include both an nvarchar and varchar pattern splitter here, but use the varchar one in the solution:

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

This does, interesting, appear to be faster that Cetin's solution with TRIM: db<>fiddle