question

kevinj avatar image
kevinj asked

Help; Create function T-SQL

Hello, i have a problem with creating a SSN control check. So the formula is as described below.


I want to have a function that can control these four types of SSN

YYMMDD-XXXX (with hyphen)

YYYYMMDD-XXXX

YYMMDDXXXX (without hyphen)

What i want is for the formula below to check if the controlnumber (last X) is correct or not

by giving me a 1 if correct and 0 if false.


Basically you get a Control number (the last X of the four Xs).
eg. like this: 8 (*2) + 9 (*1) + 1(*2) + 1(*1) + 1(*2) + 0(*1) - X(*2) X(*1)X(*2) X (birthdate 891110-XXXX)
so that gets 16 + 9 + 2 + 1 + 2 + 0 + X(*2) X(*1)X(*2) = Last X (controlnumber)

The Control number (last X) is now the value you must add to the digit sum for it to be evenly divisible by 10. If your digit sum is 21, you must add 9 for it to be 30, which is divisible by 10.

so 9 is the Control number (last X in SSN) because that what i have to add to get evenly devisible 30 (21 + 9 = 30).

Eg. so if your SSN is 891110 - 1526 its wrong which my function should show (0)
but if my SSN is 891110 - 1529 its right according to my function which should show (1)

ive tried by going by '[0-9][0-9][0-9][0-9][0-9][0-9][0-9] and also tried different DATE forms but its hard getting a formula done for me which controls the whole birthdate to get the right controlnumber.
I also have a hard time getting past the hyphen.

I hope you guys get it, and also thank you in advance :)

t-sqlsqlserver
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.