SQL-Server Function to get Sum of Integer / Money values in a String

SQL Server User Defined Function (UDF) — Get sum of Integer or Money values in a String

Here are two Microsoft SQL-Server Transact-SQL (T-SQL) user-defined functions (UDF) that scan a Varchar (String) that is composed of a sequence of fixed-length Integer (INT, SMALLINT, BIGINT) or MONEY values and sum up those values as the BIGINT or MONEY return-value of each function, respectively.

SQL-Server User Defined Function (UDF) Source Code

--********************************************************************************
--This source code is Copyright (c) 2007-2017
--     Author: Mike Eberhart
--
--I hereby release this code under the terms of the MIT License (for freeware).
--
--Permission is hereby granted, free of charge, to any person obtaining a copy
--of this software and associated documentation files (the "Software"), to deal
--in the Software without restriction, including without limitation the rights
--to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
--copies of the Software, and to permit persons to whom the Software is
--furnished to do so, subject to the following conditions:
--
--The above copyright notice and this permission notice shall be included in
--all copies or substantial portions of the Software.
--
--THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
--IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
--FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
--AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
--LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
--OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
--THE SOFTWARE.
--********************************************************************************
--**********************************************************************************************
-- Return an Integer-value containing the Summation of all Integers stored as fixed-length-char 
-- value(s) in a String representation.
--
-- This function will be quite handy for rolling / moving totals and averages, where the 
-- rolling accumulation of values, in a single-pass set-based operation, will be possible via 
-- the values stored in a string.  See moving-average sample(s) to see this in practice.
--
-- NOTES: 
--  NULL in any parameters returns null.  
--  Input String is assumed to be WELL-CRAFTED; meaning, each integer-value in the string is
--  of the @SingleValueCharLength specified, such that our parser has an easy go of things!
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SET NOCOUNT ON
--SELECT 'TEST1: '  + CASE 
--  WHEN dbo.[udfGetSumOfIntegerValuesInString](NULL,1)  IS NULL
--    THEN 'SUCCESS' ELSE 'FAIL' END; 
--SELECT 'TEST2: '  + CASE 
--  WHEN dbo.[udfGetSumOfIntegerValuesInString]('0000000123', DEFAULT)  = 123
--    THEN 'SUCCESS' ELSE 'FAIL' END; 
--SELECT 'TEST3: '  + CASE 
--  WHEN dbo.[udfGetSumOfIntegerValuesInString]('0000000123', 2)    = 24
--    THEN 'SUCCESS' ELSE 'FAIL' END; 
--SELECT 'TEST4: '  + CASE 
--  WHEN dbo.[udfGetSumOfIntegerValuesInString](' 123 456 789', 2)  = 180
--    THEN 'SUCCESS' ELSE 'FAIL' END; 
--SELECT 'TEST5: '  + CASE 
--  WHEN dbo.[udfGetSumOfIntegerValuesInString](' 123 456 789', 4)  = 1368
--    THEN 'SUCCESS' ELSE 'FAIL' END; 
--**********************************************************************************************
CREATE FUNCTION [udfGetSumOfIntegerValuesInString]
( @StringOfValues         VARCHAR(MAX),
  @SingleValueCharLength  TINYINT = 10
)
RETURNS BIGINT
AS
BEGIN
  IF  (@StringOfValues IS NULL) OR (@SingleValueCharLength IS NULL) RETURN NULL;

  DECLARE @IterationsToPerform SMALLINT = DATALENGTH(@StringOfValues) / @SingleValueCharLength;
  DECLARE @SumOfValues BIGINT = 0;
  DECLARE @LoopCount SMALLINT = 0;

  WHILE @LoopCount < @IterationsToPerform
  BEGIN
    --NOTE: we use newer += operator; for earlier SQL-Server versions, use @var = @var + ... instead
    SET @SumOfValues += 
        CONVERT(BIGINT,
                SUBSTRING(@StringOfValues, 
                @LoopCount*@SingleValueCharLength +1 , @SingleValueCharLength));
    SET @LoopCount += 1;
  END;

  RETURN @SumOfValues;

END  --Function




--**********************************************************************************************
-- Return a Money-value containing the Sum of all Money-values stored as fixed-length-char 
-- value(s) in a String representation.
--
-- This function will be quite handy for rolling / moving totals and averages, where the 
-- rolling accumulation of values, in a single-pass set-based operation, will be possible via 
-- the values stored in a string.  See moving-average sample(s) to see this in practice.
--
-- NOTES: 
--  NULL in any parameters returns null.  
--  Input String is assumed to be WELL-CRAFTED; meaning, each money-value in the string is
--  of the @SingleValueCharLength specified, such that our parser has an easy go of things!
--
-- SOME EXAMPLES / TEST CASES:
-- ===========================
--SET NOCOUNT ON
--SELECT 'TEST1: '  + CASE 
--  WHEN dbo.[udfGetSumOfMoneyValuesInString](NULL,1)             IS NULL
--    THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST2: '  + CASE 
--  WHEN dbo.[udfGetSumOfMoneyValuesInString]('000123.456', 10)   = 123.456
--    THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST3: '  + CASE 
--  WHEN dbo.[udfGetSumOfMoneyValuesInString]('0000000123', 2)    = 24
--    THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST4: '  + CASE 
--  WHEN dbo.[udfGetSumOfMoneyValuesInString](' 123 456 789', 2)  = 180
--    THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST5: '  + CASE 
--  WHEN dbo.[udfGetSumOfMoneyValuesInString](' 123 456 789', 4)  = 1368
--    THEN 'SUCCESS' ELSE 'FAIL' END;
--SELECT 'TEST6: '  + CASE 
--  WHEN dbo.[udfGetSumOfMoneyValuesInString]('1.01 4.6 700', 4)  = 705.61
--    THEN 'SUCCESS' ELSE 'FAIL' END;
--**********************************************************************************************
CREATE FUNCTION [udfGetSumOfMoneyValuesInString]
( @StringOfValues         VARCHAR(MAX),
  @SingleValueCharLength  TINYINT = 20
)
RETURNS MONEY
AS
BEGIN
  IF  (@StringOfValues IS NULL) OR (@SingleValueCharLength IS NULL)  RETURN  NULL;

  DECLARE @IterationsToPerform SMALLINT = DATALENGTH(@StringOfValues) / @SingleValueCharLength;
  DECLARE @SumOfValues MONEY = 0;
  DECLARE @LoopCount SMALLINT = 0;

  WHILE @LoopCount < @IterationsToPerform
  BEGIN
    SET @SumOfValues += 
      CONVERT(MONEY,
              SUBSTRING(@StringOfValues, 
              @LoopCount*@SingleValueCharLength +1 , @SingleValueCharLength), 2);
    SET @LoopCount += 1;
  END;

  RETURN @SumOfValues;

END  --Function

Continue to read this Software Development and Technology Blog for computer programming, software development, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, and my varied political and economic opinions.