How to get the SUM of all values in CSV in SQL Sever?

Question

I have a CSV, I want to get the SUM of all the values in it, I couldn't find a good solution.

I have thought to try STRING_SPLIT like the below, But It requires compatibility level 130 and my SQL Server is on 120. I cannot change the compatibility level.

DECLARE @csv VARCHAR(MAX) = '1,2,3,4,5';
SELECT SUM(*) FROM STRING_SPLIT(@csv, ','); --It should return 15

SQL Server details:

Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64)
Jan 25 2021 20:16:12 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

[I *did* tell you](https://stackoverflow.com/questions/66706689/how-to-set-multiple-variables-in-one-select-from-user-defined-table-type-in-sql?noredirect=1#comment117919632_66706689) it was a bad idea... Stop storing delimited values in your **scalar** variable and use a table type variable. — Thom A, Mar 19 '21 at 13:59
@Larnu I told you right, It's being used at other places, So I cant change it — Vivek Nuna, Mar 19 '21 at 14:01
But you can easily and trivially search for string splitting functions that work in prior versions. — SMor, Mar 19 '21 at 14:03
@Larnu We all know sometimes you are stuck with a bad design, either because of Management decisions, time constraints or other factors. If I tried to build everything "as it should be" I'd never finish anything. — HoneyBadger, Mar 19 '21 at 14:05
Though true, the OP here is stating they don't want to fix it, not that they cannot, @HoneyBadger . — Thom A, Mar 19 '21 at 14:10
@Larnu yes, I don't want to fix because for so many reasons which HoneyBadger has already mentioned — Vivek Nuna, Mar 19 '21 at 14:11
I don't think it pedantic at all, @HoneyBadger . Can't and won't are very different. I, for example, can't write C# because I don't know it, it's not that a won't and *do* know how to. The *real* solution here is fixing the problem of having delimited values in the first place, especially when it appears that it's affecting the OP in multiple places. They wouldn't have the problem if they didn't store delimited values; that cannot be disputed. There is a reason why almost anyone that posts a table design with delimited values will immediately be told the problem is the design; it is. — Thom A, Mar 19 '21 at 14:20
I don't want to start a discussion on what words mean @Larnu (especially on a multinational platform where people have different ideas about the subtleties of language). If OP says they can't/won't implement the *proper* (tm) solution because it would affect too many components, it is safe to assume it is a "*can't*" not a "*I don't wanna*". — HoneyBadger, Mar 19 '21 at 14:25
@Charlieface you are the champion. It's working fine. So I have few questions here now. 1. Is it safe to use `USE MASTER` between other queries in the SP (I mean SP is running in another database)? 2. What about performance?, Is it good to switch between the database in a single SP? 3. I have not tried this with my actual SP, but my SP has a transaction also and I will need to add this change in the same transaction, so is this allowed? — Vivek Nuna, Mar 19 '21 at 14:57
Performance will be the same, just make sure you qualify all table, view and function names with DB name `db.schema.table`. You can switch back after, that is no problem. AFAIK you can do this in a transaction. — Charlieface, Mar 19 '21 at 15:03
@Charlieface how did you know that it will work by `USE Master`? And how can I make sure that this will work in other servers also? — Vivek Nuna, Mar 19 '21 at 16:51
Generally the master database will be on the highest compat level available, although that is not always true — Charlieface, Mar 20 '21 at 20:01
@Charlieface I tried in the stored procedure, nut it is giving the error `a USE database statement is not allowed in a procedure, function or trigger.` — Vivek Nuna, Mar 22 '21 at 04:26

score 1 · Accepted Answer · answered Mar 19 '21 at 14:05

1

Function:

CREATE FUNCTION [dbo].[STRING_SPLIT]
(
    @string VARCHAR(MAX),
    @separator VARCHAR
)
RETURNS @returnList TABLE ([value] INT) 
AS
BEGIN
    DECLARE @location INT
    WHILE (CHARINDEX(@separator,    @string, 0) > 0)
    BEGIN
            SET @location =   CHARINDEX(@separator,    @string, 0) 
            
            INSERT INTO   @returnList
            SELECT CAST(RTRIM(LTRIM(SUBSTRING(@string,   0, @location))) AS INT)

            SET @string = STUFF(@string,   1, @location,   '') 
    END
    INSERT INTO @returnList
    SELECT CAST(RTRIM(LTRIM(@string)) AS INT)
    RETURN
END

Use:

DECLARE @csv VARCHAR(MAX) = '1,2,3,4,5';
SELECT SUM(value) FROM dbo.STRING_SPLIT(@csv, ',');

answered Mar 19 '21 at 14:05

Zunayed Shahriar

2,557
2
12
15

A `WHILE` will be, by far, the slowest way to do this. A set based method would be infinitely better. – Thom A Mar 19 '21 at 14:07
I'm not an expert. I've also found the approach from StackOverflow. I've used that in one of my projects and tweaked it a bit for this answer. – Zunayed Shahriar Mar 19 '21 at 14:24
1

I suggest having a look at this [answer](https://stackoverflow.com/a/36305493/2029983), it demonstrates a great set based solution. – Thom A Mar 19 '21 at 14:28

How to get the SUM of all values in CSV in SQL Sever?

1 Answers1