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

Recommended Answers

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') …
Jump to Post

All 4 Replies

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

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

It will start with select...

Select Cast(dbo.fn_StripNonNumeric(model) as int)
From YourTable
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.