Add some specific value in each digit of number in SQL

Question

I need the formula for calculating some number

(1) Add 3 in each digit of the particular number

(2) If we get the result after addition in two digits like 10,11,12 etc then consider only last digit

For example if number would be 564843530 then enter code here New number would be 897176023.

Shouldn't `564843530` become `897176863`? How is `5 + 3 = (1)0`? and `3 + 3 = 2`? — Thom A, Oct 30 '18 at 08:54

Zhorov · Answer 1 · 2018-10-30T10:19:14.997

Solution:

DECLARE @number int
SET @number = 564843530;

WITH DigitsTable AS 
(
    SELECT 0 AS Power10, ABS(@Number) AS Number
    UNION ALL
    SELECT Power10 + 1, Number / 10
    FROM DigitsTable
    WHERE Number > 10
)
SELECT SUM((((Number % 10) + 3) % 10) * POWER(10, Power10)) AS NewNumber
FROM DigitsTable
OPTION (MAXRECURSION 0);

Output:

897176863

Explanation:

This answer is based on one recursive CTE which returns each current digit, new digit and multiplier for calculation:

DECLARE @number int
SET @number = 564843530;

WITH DigitsTable AS 
(
    SELECT 0 AS Power10, ABS(@Number) AS Number
    UNION ALL
    SELECT Power10 + 1, Number / 10
    FROM DigitsTable
    WHERE Number > 10
)
SELECT 
    Power10,
    Number % 10 AS Digit,
    ((Number % 10) + 3) % 10 AS NewDigit,
    POWER(10, Power10) AS Multiplier
FROM DigitsTable
OPTION (MAXRECURSION 0);

Output from recursive CTE:

Power10 Digit   NewDigit    Multiplier
0       0       3           1
1       3       6           10
2       5       8           100
3       3       6           1000
4       4       7           10000
5       8       1           100000
6       4       7           1000000
7       6       9           10000000
8       5       8           100000000

gotqn · Accepted Answer · 2018-10-30T09:00:39.883

1

Ty this:

SELECT
(
    SELECT RIGHT(SUBSTRING(DS.[number], v.[number] + 1, 1) + 3, 1)
    FROM 
    (
        SELECT '564843530' 
    ) DS ([number])
    INNER JOIN [master]..spt_values v on v.[number] < LEN(DS.[number])
    WHERE V.[type] = 'P'
    ORDER BY v.[number] 
    FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)');

The algorithm is simple:

split the string to chars (in your case numbers)
add 3 to each number
using RIGHT get the first right char only
concatenate the numbers

This should work on older editions. If you are using SQL Server 2016 you can use functions like STRING_SPLIT and STRING_AGG in SQL Server 2017.

For older version you can look for SQL CLR function in order to write your own functions for splitting and concatenating. For example, in my system I can do:

Check the following link if interested in SQL CLR.

edited Oct 30 '18 at 09:00

answered Oct 30 '18 at 08:53

gotqn

42,737
46
157
243

[**Do not post images of code or errors!**](https://meta.stackoverflow.com/q/303812/995714) Images and screenshots can be a nice addition to a post, but please make sure the post is still clear and useful without them. If you post images of code or error messages make sure you also copy and paste or type the actual code/message into the post directly. – Rob Oct 30 '18 at 10:51
@Rob The solution is there as T-SQL code and can be copied. The other statement is applicable for my environment only - the screenshot display proof of concept and work. Please, read the answers first and flag them only if you understand them. – gotqn Oct 30 '18 at 11:23
@gotqn Simple answer for expected result and i am impressed with this answer bro i was writing script for this question with some additional steps,but is topped myself not to post by seeing your answer . – Sreenu131 Oct 30 '18 at 13:45
@Sreenu131 Thanks. Don't worry, there is nothing wrong to provide another solution (if it is not yet posted). – gotqn Oct 30 '18 at 13:48

score 0 · Answer 3 · answered Oct 30 '18 at 09:05

Assume that the example value supplied by the OP is wrong (as, per my comment "Shouldn't 564843530 become 897176863? How is 5 + 3 = (1)0? and 3 + 3 = 2?"), then this is my method, which makes use of NGrams8k:

WITH VTE AS (
    SELECT 564843530 AS SomeNumber),
Split AS(
    SELECT V.SomeNumber,
           NG.token,
           NG.position
    FROM VTE V
         CROSS APPLY dbo.NGrams8k(V.SomeNumber,1) NG)
SELECT SomeNumber AS OldNumber,,
       CONVERT(bigint,(SELECT token + 3
                       FROM Split x
                       WHERE S.SomeNumber = x.SomeNumber
                       ORDER BY x.position
                       FOR XML PATH(''))) AS NewNumber
FROM Split S
GROUP BY S.SomeNumber;

score 0 · Answer 4 · answered Oct 30 '18 at 09:45

Try this

DECLARE @n int, @inc int;
SELECT @n = 564843530, @inc = 3;
WITH t1(n, m, k, p)
AS (
   SELECT @n AS n, CAST(0 AS int) AS m, CAST(0 AS int) AS k, CAST(0 AS bigint) AS p
   UNION ALL
   SELECT n / 10 AS n, n - round(n, -1, 1) AS m,  k + 1 AS k, CAST(power(10, k) AS bigint) AS p
   FROM t1 WHERE n > 0
)
SELECT SUM((CASE WHEN m > 10 - @inc THEN m - 10 + @inc ELSE m + @inc END) * p)
FROM t1

Result

897176863

Add some specific value in each digit of number in SQL

4 Answers4