CODEWITHSUNDEEP
mysqlrandomgenerator
Bes Sa
Bes Sa

Reputation: 93

How to generate number sequence starting with birth date in MySQL

I need to populate my database with test data. I have a table "Citizen" with columns: 

---------------------
| CITIZEN           |
---------------------
| id   INT          |
| name VARCHAR      |
| city VARCHAR      |
| birthDate DATETIME|
| pesel INT         |
--------------------

The las column - PESEL, is a personal identity number used in Poland. It has 11 digits and first six of them are the date of birth written from the end like this: 910111 which means 11.01.1991 (11th of January). Numbers from 7-10 may be random, but the last digit is a checksum calculated by the following formula: 

 1*a + 3*b + 7*c + 9*d + 1*e + 3*f + 7*g + 9*h + 1*i + 3*j
 where letters from a to j stand for each digit of PESEL number.

Now I have all my other columns filled accept for PESEL. I need about 100 000 records so I generate them in a loop rather then do it manually. However I don't know how to determine each digit of a number and connect it with birth dates that I have in birthDate column... I would appreciate any help.

Upvotes: 1

Views: 1346

Answers (2)

dgebert
dgebert

Reputation: 1484

The solution posted by ebo is great, but there is no need to use if for number%10 = 10. It doesn't matter what is the value of "number" - there is no way to get the result equal 10.

I faced the same kind of problem with postgresql so there is a select which returns pesel for all bith_dates from table "sometable". The birth_date is in format YYYY-MM-DD

 SELECT CONCAT(A || B || C || D || E || F || G || H || I || J, 
((1*A::int + 3*B::int + 7*C::int + 9*D::int + 1*E::int + 3*F::int + 7*G::int 
+ 9*H::int + 1*I::int + 3*J::int) % 10)::text
)AS "PESEL" FROM             
(SELECT SUBSTRING(bith_date::text,3,1) AS A,
             SUBSTRING(bith_date::text,4,1) AS B,
             SUBSTRING(bith_date::text,6,1) AS C,             
             SUBSTRING(bith_date::text,7,1) AS D,             
             SUBSTRING(bith_date::text,9,1) AS E,             
             SUBSTRING(bith_date::text,10,1) AS F,                          
             FLOOR(RANDOM()*10)::text AS G, 
             FLOOR(RANDOM()*10)::text AS H,
             FLOOR(RANDOM()*10)::text AS I,
             FLOOR(RANDOM()*10)::text AS J
         FROM sometable) AS tmp

Upvotes: 0

ebo
ebo

Reputation: 2747

If you can use an external programming language you can use (for example) this Ruby library to generate a correct PESEL number for each row.

On the other hand, if you want to do this in MySQL alone you could use something like the following function (based on the Ruby library mentioned above):

CREATE FUNCTION Pesel(birthDate VARCHAR(6)) RETURNS VARCHAR(11)
BEGIN 
    DECLARE result VARCHAR(11);
    SELECT CONCAT(A, B, C, D, E, F, G, H, I, J,
           IF(((1*A + 3*B + 7*C + 9*D + 1*E + 3*F + 7*G + 9*H + 1*I + 3*J) % 10) = 10
             , 0
             ,(1*A + 3*B + 7*C + 9*D + 1*E + 3*F + 7*G + 9*H + 1*I + 3*J) % 10)) AS pesel
    INTO result
    FROM (SELECT SUBSTR(birthDate,1,1) AS A,
                 SUBSTR(birthDate,2,1) AS B,
                 SUBSTR(birthDate,3,1) AS C,             
                 SUBSTR(birthDate,4,1) AS D,             
                 SUBSTR(birthDate,5,1) AS E,             
                 SUBSTR(birthDate,6,1) AS F,                          
                 FLOOR(RAND()*10) AS G, 
                 FLOOR(RAND()*10) AS H,
                 FLOOR(RAND()*10) AS I,
                 FLOOR(RAND()*10) AS J) AS tmp;
    return result;
END

and then use that to update the column like this:

UPDATE CITIZEN
SET pesel = Pesel(DATE_FORMAT(birthDate,"%y%m%d"))

Note that I now used a VARCHAR(11) as the datetype for the pesel column, this was a bit easier to figure out the function. If you want to play with the SQL you can use this SQL Fiddle.

Upvotes: 2

Related Questions