Avatar billede graspman Nybegynder
21. januar 2011 - 12:37 Der er 11 kommentarer

SQL sætning der validerer om et cpr nummer lever op til mod 11

Hej,

Jeg har forgæves forsøgt at finde en sql der kan validerer om et cpr er validt i følge modulus 11 testen.
- Jeg ved godt at cpr.dk laver cpr numre der ikke er mod 11 mere

Jeg har en tabel som jeg gerne vil ahev renset for cpr numre der ikke lever op til mod 11 testen, men er virklig på bar bund idet jeg ikke er den store sql haj.

Tænkte der måske er nogen der har lavet den bid kode i forbindelse med et andet projekt?

Tak til alle for lidt hjælp

Hilsen G
Avatar billede showsource Seniormester
21. januar 2011 - 12:53 #1
Vil tro det er langt hurtigere at lave i dit foretrukne program. sprog.
Ex:
http://www.eksperten.dk/spm/252567
Avatar billede Syska Mester
21. januar 2011 - 12:53 #2
Jeg er ikke CPR haj, men mod af hvad ? Det hele ? eller kun det sisdte? Kan du skrive formelen ned.

mvh
Avatar billede michael_stim Ekspert
21. januar 2011 - 13:03 #3
Avatar billede graspman Nybegynder
21. januar 2011 - 13:09 #4
Hej showsource,
Det er desværre ikke en mulighed idet jeg kun kan tilgå databasen via SQL.

Jeg har tidligere trukket heletabellen ud og smidt den ind i et regneark og kørt en macro.
Men, det er meget besværligt...

Følgende macro tester og markerer cellen:

*******

CPRNR forventes at være anført som tekst

Dim antalRækker As Integer, ræk As Integer, cprNr As String
Public Sub kontrolAfCpr()
    antalRækker = ActiveCell.SpecialCells(xlLastCell).row
   
    For ræk = 1 To antalRækker
        cprNr = Range("A" & ræk)
       
        If CheckCprnr(cprNr) = False Then
            Range("A" & ræk).Interior.ColorIndex = 3
        Else
            Range("A" & ræk).Interior.ColorIndex = xlColorIndexNone
        End If
    Next ræk
End Sub
Public Function CheckCprnr(cprNr As String)
Dim cn, vgt(9), res(9), f, sum, rest, chkciff
    vgt(0) = 4
    vgt(1) = 3
    vgt(2) = 2
    vgt(3) = 7
    vgt(4) = 6
    vgt(5) = 5
    vgt(6) = 4
    vgt(7) = 3
    vgt(8) = 2

    cn = cprNr
   
    For f = 1 To 9
        res(f - 1) = Val(Mid(cn, f, 1)) * Val(vgt(f - 1))
    Next f

    sum = 0
    For f = 0 To 8
        sum = sum + res(f)
    Next f
     
    rest = sum Mod 11
   
    If rest = 0 Then
        chkciff = 0
    Else
        chkciff = 11 - rest
    End If
   
    If chkciff = Val(Right(cn, 1)) Then
        CheckCprnr = True
    Else
        CheckCprnr = False
    End If
End Function
Avatar billede HenrikSjang Nybegynder
21. januar 2011 - 13:38 #5
Du kan lave nedenstående funktion på din database, og så kalde den sådan her:
SELECT dbo.CheckCpr('1234567890')

Eller:
SELECT cprKolonne, dbo.CheckCpr(cprKolonne) as IsCprValid
FROM DinTabel

-----
CREATE FUNCTION dbo.CheckCpr (@cpr VARCHAR(10))
RETURNS INT
AS
BEGIN
DECLARE @return INT

DECLARE @1 INT = CAST(SUBSTRING(@cpr, 1, 1) AS INT)
DECLARE @2 INT =  CAST(SUBSTRING(@cpr, 2, 1) AS INT)
DECLARE @3 INT =  CAST(SUBSTRING(@cpr, 3, 1) AS INT)
DECLARE @4 INT =  CAST(SUBSTRING(@cpr, 4, 1) AS INT)
DECLARE @5 INT =  CAST(SUBSTRING(@cpr, 5, 1) AS INT)
DECLARE @6 INT =  CAST(SUBSTRING(@cpr, 6, 1) AS INT)
DECLARE @7 INT =  CAST(SUBSTRING(@cpr, 7, 1) AS INT)
DECLARE @8 INT =  CAST(SUBSTRING(@cpr, 8, 1) AS INT)
DECLARE @9 INT =  CAST(SUBSTRING(@cpr, 9, 1) AS INT)
DECLARE @10 INT =  CAST(SUBSTRING(@cpr, 10, 1) AS INT)

DECLARE @sum INT
SET @sum =
    @1 * 4 +
    @2 * 3 +
    @3 * 2 +
    @4 * 7 +
    @5 * 6 +
    @6 * 5 +
    @7 * 4 +
    @8 * 3 +
    @9 * 2 +
    @10 * 1
   
IF (@sum % 11 = 0)
    SET @return = 1
ELSE   
    SET @return = 0

RETURN @return
END
Avatar billede hrc Mester
21. januar 2011 - 13:50 #6
Måske kan dette her bruges: http://www.eksperten.dk/spm/525391 (jeg har heldigvis mulighed for at teste via et programmeringssprog). I øvrigt bør man overveje at undlade modulus-11 metoden, da CPR-registret dagligt er ved at løbe tør for valide CPR-endelser.
Avatar billede e.oersted Nybegynder
21. januar 2011 - 14:27 #7
select decode(mod((to_number(substr(cpr,1,1))*4 +
                  to_number(substr(cpr,2,1))*3 +
                  to_number(substr(cpr,3,1))*2 +
                  to_number(substr(cpr,4,1))*7 +
                  to_number(substr(cpr,5,1))*6 +
                  to_number(substr(cpr,6,1))*5 +
                  to_number(substr(cpr,7,1))*4 +
                  to_number(substr(cpr,8,1))*3 +
                  to_number(substr(cpr,9,1))*2 +
                  to_number(substr(cpr,10,1))*1 ),11),0,'Valid','Invalid') from dual

cprnummeret skal være på varchar-form og selvfølgelig skal kolonne/tabel navn ændres.
Avatar billede e.oersted Nybegynder
22. januar 2011 - 11:00 #8
Jeg var ikke lige opmærksom på at jeg var i kategorien MS SQL, så svaret ovenfor er ikke lige til at anvende (Det virker i Oracle).
Dette skulle virke umiddelbart i SQL Server:

select CASE mod((CAST(substring(cpr,1,1) AS NUMBER)*4 +
                  CAST(substring(cpr,2,1) AS NUMBER)*3 +
                  CAST(substring(cpr,3,1) AS NUMBER)*2 +
                  CAST(substring(cpr,4,1) AS NUMBER)*7 +
                  CAST(substring(cpr,5,1) AS NUMBER)*6 +
                  CAST(substring(cpr,6,1) AS NUMBER)*5 +
                  CAST(substring(cpr,7,1) AS NUMBER)*4 +
                  CAST(substring(cpr,8,1) AS NUMBER)*3 +
                  CAST(substring(cpr,9,1) AS NUMBER)*2 +
                  CAST(substring(cpr,10,1) AS NUMBER)*1 ),11)
        WHEN 0 THEN 'Valid'
        ELSE 'Invalid'
        END        from yourtable
Avatar billede graspman Nybegynder
02. februar 2011 - 13:28 #9
Undskyld det manglende svar. Jeg har været på ferie.

Tak for alle det gode svar. Går i gang med at se på dem.
Tak
Avatar billede graspman Nybegynder
02. februar 2011 - 13:41 #10
Hej e.osted.
Jeg har forsøgt med din sql men får desværre følgende fejl:

Databasefejl, Fejlkode 195 (Linjenummer 1) ('mod' is not a recognized built-in function name.)

Det må være MOD som den ikke kender.

Vh G
Avatar billede graspman Nybegynder
02. februar 2011 - 13:45 #11
Hej Sjang,

Når jeg kører din sql får jeg følgende fejlmeddelse om

"Ikke tilladt sqlsætning - Ugyldig nøgleord i forspørgsel: "BEGIN" ved position 54."

Jeg formoder det er fordi der er restriktioner på DB og det kun er tilladt at køre rene select sætninger på DB.

- Nogen andre forslag?


Hilsen G
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester