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

I found a website that

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-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()))
-- 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.

CodePudding user response：

Firstly, your current logic is flawed, you get a random number between 0 and 9999, however you don't make that value fixed width. As a result, if your random numbers were 7, 9, and 2, you'd end up with 792 not 00700090002. You need to add the leading zeros. I do this with CONCAT and RIGHT.

Next I move the expression into the FROM so that it's materialised and easily usable. Then we can use SUBSTRING and CONVERT to get the 2 parts of your number and SUM them, and then finally apply the final logic. I then inject the periods into the string.

SELECT V.NIN,
       STUFF(STUFF(STUFF(CONCAT(V.NIN,10 - ((Odds   (Evens * 3)) % 10)),12,0,'.'),8,0,'.'),4,0,'.')
FROM (VALUES(CONCAT(RIGHT(CONCAT('000','756'),3), 
                    RIGHT(CONCAT('0000',FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000 1) 1000)),4),
                    RIGHT(CONCAT('0000',FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000 1) 1000)),4),
                    ABS(CHECKSUM(NEWID())))),
            ('756123456789'))V(NIN)
     CROSS APPLY (VALUES(CONVERT(int,SUBSTRING(V.NIN,1,1))   CONVERT(int,SUBSTRING(V.NIN,3,1))   CONVERT(int,SUBSTRING(V.NIN,5,1))   CONVERT(int,SUBSTRING(V.NIN,7,1))   CONVERT(int,SUBSTRING(V.NIN,9,1))   CONVERT(int,SUBSTRING(V.NIN,11,1)),
                         CONVERT(int,SUBSTRING(V.NIN,2,1))   CONVERT(int,SUBSTRING(V.NIN,4,1))   CONVERT(int,SUBSTRING(V.NIN,6,1))   CONVERT(int,SUBSTRING(V.NIN,8,1))   CONVERT(int,SUBSTRING(V.NIN,10,1))   CONVERT(int,SUBSTRING(V.NIN,12,1))))I(Odds,Evens)

Honestly, however, I would suggest that this is probably something for your application, not SQL.

CodePudding user response：

Another method to generate a random SNIN with a control number.

The trick used is a cross apply, with number values to calculate the sums of odds and evens.

select concat(code, ctrl) as SNIN
from (values
  (concat('756','.', right(format(rand()*100000,'0000'),4)
               ,'.', right(format(rand()*100000,'0000'),4)
               ,'.', right(format(rand()*100,'0'),1))) 
, ('756.1234.5678.9')
, ('756.1917.1051.7')
) v(code)
cross apply (
  select (10-(sum(odds) (3*sum(evens)))) as ctrl
  from (
    select 
      cast(substring(code_nr,(n*2) 1,1) as int) as odds
    , cast(substring(code_nr,(n 1)*2,1) as int) as evens
    from (values (0),(1),(2),(3),(4),(5)) as nums(n)
    cross join (select replace(code,'.','')) as code(code_nr)
  ) q
) ca;

Demo on db<>fiddle here

CodePudding user response：

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

Note: I have added  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  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   1 )   1000 ) AS INT ),
                CAST( FLOOR( RAND( CHECKSUM( NEWID())) * ( 9999 - 1000   1 )   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