Use stored procedures in SQL Server to verify that the ID number is legitimate

In some personnel information system, may ask to fill in the person's identity card number, but how to verify the format of the ID number is legal? We write the validated SQL as a stored procedure to facilitate repeated calls.

First of all, we need to understand the composition of our identity number rules:
1, 18-digit ID number composition: 6-digit Area code + 8-digit date of birth + 3 digit number (odd for male, even for female) + 1-bit check code
2, 15-digit ID number composition: 6-digit Area code + 6-digit date of birth + 3 digit number (odd for male, even for female)

The following is all the SQL for this stored procedure

-- =============================================
--Author:<author,,name >
--Create Date: <create date, >
--Description:<description,, >
-- =============================================
Create FUNCTION [dbo]. [Udf_isvalididcard]
(
@IDCardNo varchar (50) = "
)
RETURNS bit
As
/*******************************************************************
Function name: Udf_isvalididcard ()
Parameter: @IDCardNo string ID number
Return value: bit is valid
Function Description: Determine whether the ID card number is legal

Note: At present, China's identity card number is 18 and 15 digits.
1, 18-digit ID number composition: 6-digit Area code + 8-digit date of birth + 3 digit number (odd male) + 1-bit check code
2, 15-digit ID number composition: 6-digit Area code + 6-digit date of birth + 3 digit number (odd male puppet)
*******************************************************************/
BEGIN

DECLARE @Length int,
@Loop int,
@Sum int
DECLARE @SingleChar Char

Set @Sum = 0
If @IDCardNo is null or @IDCardNo = NULL or LTrim (RTrim (@IDCardNo)) = "
Begin
return 0
End

Set @Length = Len (@IDCardNo)
--judging the number of digits
If @Length < > @Length < > 15
Begin
return 0
End
If @Length = 18
Begin
If IsNumeric (left (@IDCardNo, 17)) = 0
Begin
return 0
End
If IsDate (substring (@IDCardNo, 7, 4) + '-' + substring (@IDCardNo, one, 2) + '-' + substring (@IDCardNo, 13, 2)) = 0
Begin
return 0
End
Set @Loop = 17
while (@Loop >= 1)
Begin
Set @Sum = @Sum + convert (int,substring (@IDCardNo, @Loop, 1)) * (Power (2, (18-@Loop))% 11)
Set @Loop = @Loop-1
End
Set @Loop = @Sum% 11
If @Loop = 0
Begin
Set @SingleChar = ' 1 '
End
else If @Loop = 1
Begin
Set @SingleChar = ' 0 '
End
else If @Loop = 2
Begin
Set @SingleChar = ' X '
End
Else
Begin
Set @SingleChar = CONVERT (varchar (2), (12-@Loop))
End
If Lower (right (@IDCardNo, 1)) < > lower (@SingleChar)
Begin
return 0
End
End
else If @Length = 15
Begin
If IsNumeric (@IDCardNo) = 0
Begin
return 0
End
If IsDate (' + ' + substring (@IDCardNo, 7, 2) + '-' + substring (@IDCardNo, 9, 2) + '-' + substring (@IDCardNo, 11, 2)) = 0
Begin
return 0
End
End

Return 1

END

Once created, we can use the following SQL tune to test for correctness:

DECLARE @result bit
EXEC @result =dbo.udf_isvalididcard ' ID number '
Select @result

Returns 1, representing the correct ID number,
Returns 0, which represents an incorrect ID card number.

A brief explanation of the system functions used above means:
Power Functions: Returns the value of the specified power of the given expression.
For example: Select Power (2,3) returns 2 to the power of 3, or 8

Lower function: Returns a character expression when the uppercase character data is converted to lowercase.
For example: Select LOWER (' abc '), Return ' ABC '

substring function: Returns the substring of the $sourceString, starting at the position specified by the $STARTINGLOC, and the length of the number of characters specified in the $length.
For example: Select substring (' ABCD '), return ' AB '
Note: This function is very interesting
1, the index of the $sourceString parameter is calculated starting from 1.
2, if you are starting from the 0 position value, will not be wrong, but will only return ' a '
3, if you are starting from the 1 position to take the value, also can not be wrong, but return empty, but the $length data to fill some, and return the value.
From the above test it should be possible to conclude that if $startingloc is less than or equal to 0, then the substring parameter becomes
String ($sourceString, 1, $length -1+ $startingLoc)
That is, select substring (' ABCD ', 0,2) ==select substring (' ABCD ', 1, 1),
Select substring (' ABCD ', -1,2) ==select substring (' ABCD ', 1,2-1+ (-1))
Select substring (' ABCD ', -1,3) ==select substring (' ABCD ', 1,3-1+ (-1))
The indeterminate formula is like this, but the return result is the same as this one.

IsDate Function: Determines whether an input expression is a valid date.

IsNumeric Function: Determines whether an expression is a valid numeric type.

Convert function: An expression that explicitly converts an expression of one data type to another data type.

Use stored procedures in SQL Server to verify that the ID number is legitimate