CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
user3583912
user3583912

Reputation: 1322

T-SQL how to SUM a text data?

I got a field value 0+6+6+6+0+0+0 data type is varchar. how can I get sum of it.

I have tried to convert to int but I got error.

select cast('0+6+6+6+0+0+0' as int)

I want output as 18.

Upvotes: 4

Views: 8029

Answers (6)

Nick Kapatais
Nick Kapatais

Reputation: 336

This answer is in no way re-usable but by providing a re-usable solution I think I will be encouraging bad data.

This will do what you want, for now.

DECLARE @fullString VARCHAR (20) = '0+6+6+6+0+0+0'

DECLARE @firstNo INT = CAST(SUBSTRING(@fullString,3,1) AS INT)
DECLARE @secondNo INT = CAST(SUBSTRING(@fullString,5,1) AS INT)
DECLARE @thirdNo INT = CAST(SUBSTRING(@fullString,7,1) AS INT)

SELECT
    @firstNo + @secondNo + @thirdNo AS 'Total'

Your number isn't big, you can easily cast three numbers and add them. Ideally, no value such as '0+6+6+6+0+0+0' should exist in one column. Judging by what you want to do, I imagine that each number represents something different so each number should be stored as such. I'd focus on solving the actual problem and fixing the data. Even if you find a way to work with this data now, you'll shoot yourself in the foot sooner or later.

Upvotes: 1

Sameer
Sameer

Reputation: 391

Simply go for below code:

 SELECT SUM(CAST(VALUE AS INT)) FROM STRING_SPLIT('0+6+6+6+0+0+0','+')

Upvotes: 2

lije
lije

Reputation: 420

If you will have such incident repeatedly, you can create a function that will split the numbers from the delimited text. Then you can apply SUM casting the column to integer. This is how it works: 

CREATE FUNCTION [dbo].[splittext] 
   (@delimiter VARCHAR(5), 
      @text      VARCHAR(MAX)
   ) 
   RETURNS @values table
      (Id   SMALLINT IDENTITY(1,1), 
       delimitedvalue VARCHAR(MAX) 
      ) 
AS 
   BEGIN

      DECLARE @len INT

      WHILE LEN(@text) > 0 
         BEGIN 

            SELECT @len = (CASE CHARINDEX(@delimiter, @text) WHEN 0 THEN LEN(@text) 
            ELSE (CHARINDEX(@delimiter, @text) -1)END) 

            INSERT INTO @values 
            SELECT SUBSTRING(@text, 1, @len )

            SELECT @text = (CASE (LEN( @text )- @len) WHEN 0 THEN '' ELSE RIGHT(@text, LEN(@text) - @len - 1) END) 
         END

      RETURN 

   END

Then to get the SUM: 

SELECT  SUM(CAST(delimitedvalue AS INT)) FROM dbo.[splittext] ('+', '0+6+6+6+0+0+0')

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 82020

Assuming you want to perform the calculation on every record in your table.

Example

Declare @YOurTable table (ToEval varchar(50))
Insert Into @YOurTable values
 ('0+6+6+6+0+0+0')
,('5+6+25')

Select A.*
      ,B.*
 From  @YOurTable A
 Cross Apply (  
                Select Value = sum(Value)
                 From  (
                        Select  Value = B.i.value('(./text())[1]', 'int')
                        From  (Select x = Cast('<x>' + replace(ToEval,'+','</x><x>')+'</x>' as xml)) A 
                        Cross Apply x.nodes('x') AS B(i)
                       ) B1
             ) B

Returns

ToEval          Value
0+6+6+6+0+0+0   18
5+6+25          36

Upvotes: 4

indika ranaweera
indika ranaweera

Reputation: 116

If you are using SQL server 2016 and above you could use following 

        DECLARE @NumbersString VARCHAR(1000) = '0+6+6+6+0+0+0'

        SELECT  SUM(cast(value as int)) 
        FROM    string_split(@NumbersString,'+')

if you are using MS Sql version below 2016

        DECLARE @NumbersString VARCHAR(MAX) = '0+6+6+6+0+0+0'

        SELECT SUM(cast(Split.a.value('.', 'NVARCHAR(MAX)') as int)) DATA
        FROM
        (
            SELECT CAST('<X>'+REPLACE(@NumbersString, '+', '</X><X>')+'</X>' AS XML) AS String
        ) AS A
        CROSS APPLY String.nodes('/X') AS Split(a);

Upvotes: 3

SMor
SMor

Reputation: 2882

As John states, dynamic sql will do this: 

declare @x varchar(20) = '0+6+6+6+0+0+0';
exec ('select ' + @x + ' as answer');

But i'll guess that you have over-simplified things. And perhaps the bigger question is why you have a formula in a string that you need to compute using tsql.

Upvotes: 4

Related Questions