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