T-SQL :: generate random Swiss National Identification Number (AHV/AVS)

I woul like to generate random Swiss National Identification Number (AHV/AVS).

I found a website that can do that and if I look at the source code of the same page I can alos see the JavaScript code that can generate it.

The numbers are generated following this pattern:

1. 756: is the prefix number, it never changes
2. 1234: is a random number
3. 5678: is a random number
4. 9: is a random number
5. 7: is the control number that is generated by this calculation:
   • Starting at the first number, take every other number and sum them: 7 + 6 + 2 + 4 + 6 + 8 = 33
   • Starting at the drcond number, take every other number and sum them: 5 + 1 + 3 + 5 + 7 + 9 = 30
   • Then multiply the second number x 3 and sum the first number: 33 + (30 x 3) = 123
   • Now make 10 minus the modulo of that number: 10-(123%10) = 10-3 = 7

===> And this is how we finally have obtained 7 which is the last number <===

I have created a SQL command that can generate the random number I need:

SELECT CONCAT('756.', 
FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000+1)+1000) , '.',
FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000+1)+1000) , '.',
ABS(CHECKSUM(NEWID()))%10
-- How to select one number out of two? 
)

This code generates all random numbers that I need but I’m missing how to select one number out of two.

I don’t know if T-SQL can parse numbers and select one out of two.

Answer

This implementation uses integer calculations instead of substring to get digits.

Note: I have added %10 to the final result of check digit calculation to convert 10 -> 0.

-- Format Number
SELECT *, CAST( Prefix AS CHAR( 3 )) + '.' + CAST( Rnd1 AS CHAR( 4 )) + '.' + CAST( Rnd2 AS CHAR( 4 )) + '.' + CAST( Rnd3 AS CHAR( 1 )) + CAST( CheckDigit AS CHAR( 1 )) AS FinalNum
FROM(
    -- Step 2: calculate check digit. NOTE: I apply %10 to the result to convert 10 to 0
    SELECT *, ( 10 - (( OddDigits + ( EvenDigits ) * 3 ) % 10 )) % 10 AS CheckDigit
    FROM 
        -- Step 2: calculate Odd / Even digit sum. Note that prefix is hardcoded to 756
        ( SELECT Prefix, Rnd1, Rnd2, Rnd3,
            7 + 6 + (( Rnd1 % 1000 ) / 100 ) + ( Rnd1 % 10 ) + (( Rnd2 % 1000 ) / 100 ) + ( Rnd2 % 10 ) AS OddDigits,
            5 + ( Rnd1 / 1000 ) + (( Rnd1 % 100 ) / 10 ) + ( Rnd2 / 1000 ) + (( Rnd2 % 100 ) / 10 ) + Rnd3 AS EvenDigits
        FROM
            -- Step 1: initial random values. Note that prefix is hardcoded to 756
            ( VALUES( 756,
                CAST( FLOOR( RAND( CHECKSUM( NEWID())) * ( 9999 - 1000 ) + 1000 ) AS INT ),
                CAST( FLOOR( RAND( CHECKSUM( NEWID())) * ( 9999 - 1000 ) + 1000 ) AS INT ),
                ABS( CHECKSUM( NEWID())) % 10 ),
                -- Your original example. Should be removed in production code
                (756, 1234, 5678, 9))   AS RndNum( Prefix, Rnd1, Rnd2, Rnd3 )
        ) AS CtrlDigitCalculation
    ) AS FormattedNumber

Result

Prefix      Rnd1        Rnd2        Rnd3        OddDigits   EvenDigits  CheckDigit  FinalNum
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------------
756         3826        4185        8           34          34          9           756.8002.7335.52
756         1234        5678        9           33          30          7           756.1234.5678.97

Update

Checked the return value of random number generator expression and found that it can return a 5 digit value i.e. 10000.

SELECT CAST( FLOOR( CAST( 1.0 AS FLOAT ) * ( 9999 - 1000 + 1 ) + 1000 ) AS INT )
-- Result: 10000

To fix it you need to remove “+ 1”:

SELECT CAST( FLOOR( CAST( 1.0 AS FLOAT ) * ( 9999 - 1000 ) + 1000 ) AS INT )
-- Result: 9999