Even and odd place values in SQL Server

Question

multiply all even place values by 2 (excluding zeros) and odd place values by 1.

0012345

0*2+0*1+1*2+2*1+3*2+4*1+5*2
0+0+2+2+6+4+10
0+0+2+2+6+4+(1+0)
15 = 1+5 = 6

The total of a given number is 6

Answer 1

Hi @RajKumar

please check this function ：

create function [dbo].[TESTFUNC](@string VARCHAR(MAX))-- parameter  
returns int --return type  
AS  
BEGIN   
  DECLARE @LENGTH INT = (SELECT LEN(@string))   
  declare @returnvalue int   
  IF @LENGTH <3  
    BEGIN  
     DECLARE @stringint INT = CONVERT(INT,@string)  
	 SET @returnvalue = (SELECT IIF(@stringint>=10,@stringint/10+@stringint%10,@stringint))  
	END  
  ELSE  
    BEGIN  
     DECLARE @I INT = 1  
	 DECLARE @STR VARCHAR(10)  
	 DECLARE @SUM INT = 0  
	 WHILE @I <=@LENGTH  
	 BEGIN   
  
		SET @STR  = SUBSTRING(@string,@I,1)  
		DECLARE @STRVALUE INT= CONVERT(INT,@STR)  
    
		DECLARE @IFEVEN int =    IIF(@I%2=0,1,0)  
  
		SET @STRVALUE = IIF(@IFEVEN =0,@STRVALUE*2,@STRVALUE *1)  
		SET @STRVALUE =   IIF(@STRVALUE>=10,@STRVALUE/10+@STRVALUE%10,@STRVALUE)  
  
   
    
		SET @I = @I+1  
		SET @SUM = @SUM+@STRVALUE  
	  END  
	  declare @strtemp VARCHAR(MAX)= cast(@SUM AS VARCHAR(MAX))  
	  SET @returnvalue  = dbo.TESTFUNC(@strtemp)  
      END  
   RETURN @returnvalue  
END  

and use it

SELECT dbo.TESTFUNC('0012345')  

result：

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

Hi @RajKumar ,

You cannot pass a BIGINT data type to the function. It is because of the leading zeros.

Please try the following solution. It will give you a good jumpstart for your function.
Hint: A UDF function could be called recursively.

SQL

DECLARE @inputValue VARCHAR(20) = '0012345';  
  
DECLARE @tbl TABLE (Data VARCHAR(20));  
INSERT INTO @tbl VALUES (@inputValue);  
  
; WITH rs AS  
(  
 SELECT digit = SUBSTRING(@inputValue, number, 1)  
 , seq = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  
 FROM @tbl CROSS APPLY  
 (  
 SELECT DISTINCT  
    number  
 FROM master..spt_values  
 WHERE number > 0  
   AND number <= LEN(@inputValue)  
 ) AS V  
)  
SELECT Result = SUM(IIF(seq % 2 = 1, digit*2, digit))  
FROM rs;