身份證、組織機構代碼及營業執照號校正函數

標籤：tiny   alt   arch   substring   string   ide   功能   case   validate   

GO
/****** Object: UserDefinedFunction [ro].[wj_ValidateIDC] Script Date: 09/29/2012 11:23:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--驗證社會安全號碼碼
--編者：
--建立日期：2012-09-18
--參數：@idc 社會安全號碼
create FUNCTION [ro].[wj_ValidateIDC]
(
@idc varchar(18)
)RETURNS BIT
AS
BEGIN

DECLARE @validFactors VARCHAR(17),@validCodes VARCHAR(11),@i TINYINT,@iTemp INT
SELECT @validFactors=‘79A584216379A5842‘,@validCodes=‘10X98765432‘,@i=1,@iTemp=0
--驗證校正位
--根據擔保資料庫增加條件，否則報錯，by WJ
IF (@idc LIKE ‘x%‘ OR @idc LIKE ‘*%‘ OR @idc LIKE ‘T%‘)
RETURN(0)
IF LEN(@idc)<>15 AND LEN(@idc)<>18--社會安全號碼只有15或18位
RETURN(0)
IF LEN(@idc)=15 --如果是15位身份證 則只驗證日期
IF (ISDATE(‘19‘+SUBSTRING(@idc,7,6))=0 and ‘19‘+SUBSTRING(@idc,7,6) between ‘1900-01-01‘ and ‘2010-01-01‘)
RETURN(0)
ELSE
RETURN(1)
--18位身份證 驗證日期 校正位
IF (ISDATE(SUBSTRING(@idc,7,8))=0 and SUBSTRING(@idc,7,6) between ‘1900-01-01‘ and ‘2010-01-01‘)--驗證日期
RETURN(0)
---驗證校正位開始

WHILE @i<18
BEGIN
SELECT @[email protected]+CAST(SUBSTRING(@idc,@i,1) AS INT)*
(CASE SUBSTRING(@validFactors,@i,1) WHEN ‘A‘ THEN 10 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
--功能：驗證營業執照號
--編者：
--建立日期：2012-09-20
--參數：@ZzNo 營業執照號
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(@ZzNo)))=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 *2
SET @p = @p%@n
SET @[email protected]+1
END
SET @p = @p+SUBSTRING(@ZzNo,15,1)
SET @p = @p%@m

IF @p=1
BEGIN
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
--功能：組織機構號驗證
--編者：
--建立日期：2012-09-20
--參數：@ZZJGNo 組織機構號
ALTER FUNCTION [ro].[wj_ValidateZZJGNo]
(
@ZZJGNo varchar(20)
)RETURNS BIT
AS
BEGIN
DECLARE @c varchar(20),@zz int,@z int,@i INT,@jaz VARCHAR(30),@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 WHEN 1 THEN 3 WHEN 2 THEN 7 WHEN 3 THEN 9 WHEN 4 THEN 10 WHEN 5 THEN 5 WHEN 6 THEN 8 WHEN 7 THEN 4 WHEN 8 THEN 2 END)
END
ELSE IF @c>=‘0‘ AND @c<=‘9‘
BEGIN
SET @z=CONVERT(NUMERIC,@c) * (CASE @i WHEN 1 THEN 3 WHEN 2 THEN 7 WHEN 3 THEN 9 WHEN 4 THEN 10 WHEN 5 THEN 5 WHEN 6 THEN 8 WHEN 7 THEN 4 WHEN 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

身份證、組織機構代碼及營業執照號校正函數