Calculate the sum of digits

[jorge_oliveira]

Hello

I'm having a problem to calculate the sum of digits from a field.
Example: 1234
I want to sum 1+2+3+4, in order to obtain the result 10.

Using the following query I obtain the result, but only by mere coincidence:
SELECT model, CONVERT (NUMERIC(6,0), SUBSTRING(model,1,1)*1.0+SUBSTRING(model,2,1)*1.0+SUBSTRING(model,3,1)*1.0+SUBSTRING(model,4,1)*1.0)
FROM product

The column (model) may contain not only digits, but also non-digit symbols according to the data type of the column - varchar (50).
Also, if a model does not contain digits at all , the result should be 0.

Thanks for the help!

Jorge

[sknake]

Can you post some sample data? By what you have said I am under the impression that "h1n2c3n5" should return the result of 1+2+3+5 which equals 11? Is that correct?

This udf will strip out all non-numerics for you which you can safely convert:

if object_id('dbo.fn_StripNonNumeric') is not null
   drop function dbo.fn_StripNonNumeric 
go
create function dbo.fn_StripNonNumeric (@Input nvarchar(max))
returns nvarchar(max)
as
begin
     while patindex('%[^0-9]%',@input)>0
          set @input = substring(@input,1,patindex('%[^0-9]%',@input)-1) +  substring(@input,patindex('%[^0-9]%',@input)+1, (datalength(@input)/2))
return @input
end
go
select dbo.fn_StripNonNumeric ('1234xasdf66')

Taken from:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24054766.html

[jorge_oliveira]

Thanks for the answer!

The problem is that the query has to start with SELECT.

This is an exercise from http://www.sql-ex.ru/exercises.php?N=4

The data is something like this:
model
1232
1233
1276
1298
(...)

All of the above have 4 digits, but, as I said, a field can have letters and more than 4 characters!

Thanks again
Jorge

[sknake]

It will start with select...

Select Cast(dbo.fn_StripNonNumeric(model) as int)
From YourTable

[ergen]

And another approach to the problem: http://jacek-szarapa.com/index.php?p=sql&d=36