I'm having a problem to calculate the sum of digits from a field.
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)
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!