tsql : sum of string

coool sweet 61 Reputation points
2021-07-07T19:47:01.067+00:00

hi

i have strings like this

123432453212
1dddddddddd
1gh45j2dungj

what i need is sum of each numbers and ignore characters or replace it with 0 for sum

example : 123432453212 = 32
1dddddddddd = 1
1gh45j2dungj = 12

any help

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-07-07T19:57:32.383+00:00
    DECLARE @str varchar(20) = '1234abc99cc'
    SELECT SUM(try_cast(substring(@str, n, 1) AS int))
    FROM   Numbers
    WHERE  n BETWEEN 1 AND len(@str)
    

    Numbers here is a one-column table with rows from 1, 2, 3 etc. See this article of mine how to create it and what other use you may have of it: https://www.sommarskog.se/Short%20Stories/table-of-numbers.html

    1 person found this answer helpful.
    0 comments No comments

  2. coool sweet 61 Reputation points
    2021-07-07T20:23:32.737+00:00

    is there any other way without creating numbers table here?


  3. Viorel 111.8K Reputation points
    2021-07-07T21:18:49.5+00:00

    If you are not interested in writing queries only, then consider a loop:

    declare @string varchar(max) = '1gh45j2dungj'
    
    declare @sum int = 0
    declare @i int = len(@string)
    
    while @i > 0
    begin
        set @sum += isnull(try_cast(substring(@string, @i, 1) as int), 0)
        set @i -= 1
    end
    
    select @sum
    
    0 comments No comments

  4. Yitzhak Khabinsky 24,936 Reputation points
    2021-07-08T00:15:08.647+00:00

    Hi @coool sweet ,

    Please try the following solution.

    SQL

    -- DDL and sample data population, start  
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(1024));  
    INSERT INTO @tbl (tokens) VALUES  
    ('123432453212'),  
    ('1dddddddddd'),  
    ('1gh45j2dungj'),  
    ('fafa');  
    -- DDL and sample data population, end  
      
    SELECT p.ID, p.tokens, (  
    	SELECT SUBSTRING(tokens, number, 1)  
    	FROM @tbl AS c  
    		CROSS APPLY (  
    			SELECT DISTINCT number  
    			FROM master..spt_values  
    			WHERE number BETWEEN 1 AND LEN(tokens)  
    		) V  
    	WHERE c.ID = p.ID  
    	FOR XML PATH('r'), TYPE, ROOT('root')  
    ).value('sum(/root/r/text()) cast as xs:unsignedLong?', 'INT') AS Summary  
    FROM @tbl AS p;  
    

    Output

    +----+--------------+---------+  
    | ID |    tokens    | Summary |  
    +----+--------------+---------+  
    |  1 | 123432453212 |      32 |  
    |  2 | 1dddddddddd  |       1 |  
    |  3 | 1gh45j2dungj |      12 |  
    |  4 | fafa         |       0 |  
    +----+--------------+---------+  
    
    0 comments No comments

  5. EchoLiu-MSFT 14,571 Reputation points
    2021-07-08T03:37:42.847+00:00

    Hi @coool sweet ,

    Viorel-1's method looks great. I modified it into a function, which is more convenient to use, please refer to:

    CREATE TABLE #test(string varchar(25))  
    INSERT INTO #test VALUES('123432453212'),('1dddddddddd'),('1gh45j2dungj')  
       
     create  function [dbo].[Getsum](@UDF02 varchar(40)) returns int  
     as  
     begin  
     declare @string varchar(max)  
     set @string =@UDF02  
     declare @sum int = 0  
     declare @i int = len(@string)  
          
     while @i > 0  
     begin  
         set @sum += isnull(try_cast(substring(@string, @i, 1) as int), 0)  
         set @i -= 1  
      end  
      
    return @sum  
    end  
      
    SELECT *,[dbo].[Getsum](string)   
    FROM #test  
      
    DROP FUNCTION [dbo].[Getsum]  
    

    Output:
    112799-image.png

    This answer should have appeared in the comment, but due to the character limitation of the comment, it appears here.

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments