ID card, organization code and business license number check function

Source: Internet
Author: User
Tags rtrim

GO
/****** object:userdefinedfunction [ro]. [WJ_VALIDATEIDC] Script date:09/29/2012 11:23:34 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
--Verify the ID number
--Editor:
--Date Created: 2012-09-18
--Parameters: @idc ID number
Create FUNCTION [ro]. [WJ_VALIDATEIDC]
(
@idc varchar (18)
) RETURNS BIT
As
BEGIN

DECLARE @validFactors varchar (), @validCodes varchar (one), @i TINYINT, @iTemp INT
SELECT @validFactors = ' 79a584216379a5842 ', @validCodes = ' 10x98765432 ', @i=1, @iTemp =0
--Verifying the check digit
--According to the guarantee database to increase the conditions, otherwise error, by WJ
IF (@idc like ' x ' or @idc like ' *% ' or @idc like ' t% ')
RETURN (0)
If LEN (@idc) <>15 and LEN (@idc) <>18--ID number only 15 or 18 bits
RETURN (0)
If LEN (@idc) =15--only validates the date if it is a 15-digit ID
IF (ISDATE (' +substring (@idc, 7,6)) =0 and ' +substring ' (@idc, 7,6) between ' 1900-01-01 ' and ' 2010-01-01 ')
RETURN (0)
ELSE
RETURN (1)
--18-digit ID verification date check digit
IF (ISDATE (SUBSTRING (@idc, 7,8)) =0 and SUBSTRING (@idc, 7,6) between ' 1900-01-01 ' and ' 2010-01-01 ')--Verify Date
RETURN (0)
---Verify that the check bit starts

While @i<18
BEGIN
SELECT @[email protected]+cast (SUBSTRING (@idc, @i,1) as INT) *
(Case SUBSTRING (@validFactors, @i,1) when ' A ' and then ' ELSE SUBSTRING (@validFactors, @i,1) END)
, @[email protected]+1
END
IF SUBSTRING (@validCodes, @iTemp%11+1,1) =right (@idc, 1)
RETURN 1
ELSE
RETURN 0
RETURN 0
END


GO
/****** object:userdefinedfunction [ro]. [Wj_validateyyzzno] Script date:09/29/2012 11:25:49 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
--Function: Verify business license number
--Editor:
--Date Created: 2012-09-20
--Parameters: @ZzNo Business License number
Create FUNCTION [ro]. [Wj_validateyyzzno]
(
@ZzNo varchar (15)
) RETURNS BIT
As
BEGIN

DECLARE @m int, @n int, @s int, @p int, @i int, @result INT
SELECT @m=10,@n=11,@[email protected]

if (LEN (LTRIM (RTRIM (@ZzNo))) =13 and IsNumeric (@ZzNo) =1)
Begin
RETURN 1
END
IF (IsNumeric (LTRIM (RTRIM zNo)) =0)
Begin
Return 0
End
IF (LEN (LTRIM (RTRIM (@ZzNo))) <>15)
Begin
Return 0
End

Set @i=1
while @i <=14
BEGIN
SET @s = SUBSTRING (@ZzNo, @i,1)
SET @p = @[email protected]
Set @p [email protected]%@m
IF @p = 0
BEGIN
Set @p= @m
END
Set @p = @p *
Set @p = @p%@n
set @[email protected]+1
END
Set @p = @p+substring (@ZzNo, 15,1)
SET @p = @p%@m

IF @p=1
Be GIN
Set @result =1
END
ELSE
BEGIN
SET @result =0
End
RETURN @result
End

GO
/****** object:userdefinedfunction [ro]. [Wj_validatezzjgno] Script date:09/29/2012 11:26:32 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
--Function: Organization number verification
--Editor:
--Date Created: 2012-09-20
--Parameters: @ZZJGNo Organization number
ALTER FUNCTION [ro]. [Wj_validatezzjgno]
(
@ZZJGNo varchar (20)
) RETURNS BIT
As
BEGIN
DECLARE @c varchar, @zz int,@z int,@i int, @jaz varchar (@C9 varchar (10)
SET @ZZJGNo = UPPER (@ZZJGNo)

SET @i=1
While @i <=8
BEGIN
SET @c= SUBSTRING (@ZZJGNo, @i,1)
IF @c>= ' A ' and @c<= ' Z '
BEGIN
SET @z = (ASCII (@c) -55) * (case @i If 1 then 3 if 2 then 7 if 3 then 9 if 4 then ten when 5 then 5 if 6 then 8 WH EN 7 then 4 if 8 then 2 END)
END
ELSE IF @c>= ' 0 ' and @c<= ' 9 '
BEGIN
SET @z=convert (numeric,@c) * (case @i if 1 then 3 when 2 then 7 when 3 then 9 when 4 then ten when 5 then 5 if 6 then 8 When 7 then 4 if 8 then 2 END)

END
ELSE
BEGIN
RETURN 0
END
SET @zz = ISNULL (@zz, 0) [email protected]
SET @i = @i+1
END


SET @jaz = 11-(@zz%11)
IF @jaz =10
BEGIN
SET @C9 = ' X '
END
ELSE IF @jaz =11
BEGIN
SET @C9 = ' 0 '
END
ELSE
BEGIN
SET @C9 =ltrim (RTRIM (@jaz))
END


IF @ZZJGNO = SUBSTRING (@ZZJGNO, 1,8) [email protected]
BEGIN
RETURN 1
END
IF @ZZJGNO = SUBSTRING (@ZZJGNO, 1,8) + '-' [email protected]
BEGIN
RETURN 1
END
IF @ZZJGNO = SUBSTRING (@ZZJGNO, 1,8) + '-' [email protected]
BEGIN
RETURN 1
END
IF @ZZJGNO = SUBSTRING (@ZZJGNO, 1,8) + "[Email protected]
BEGIN
RETURN 1
END
RETURN 0
END

ID card, organization code and business license number check function

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.