CREATE FUNCTION [dbo].[Check_ID] 
(
 @ID nvarchar(10)
)
RETURNS nvarchar(20)
AS
BEGIN
declare @i int,@j int,@Total int,@ID1 nvarchar(1),@info nvarchar(20);
set @i=8;set @j=2;Set @Total=0;set @info='';
if(LEN(@ID)!=10)
begin
 set @info=N'字串不為10碼!';
end
else 
begin
set @ID1=SUBSTRING(@ID,1,1);
--
set @Total=(case when @ID1='A' then 1--'10' 1*[1]+0*[9]
            when @ID1='B' then 10--'11' 1*[1]+1*[9]
            when @ID1='C' then 19--'12'
            when @ID1='D' then 28--'13'
            when @ID1='E' then 37--'14'
            when @ID1='F' then 46--'15'
            when @ID1='G' then 55--'16'
            when @ID1='H' then 64--'17'
            when @ID1='J' then 73--'18'
            when @ID1='K' then 82--'19'
            when @ID1='L' then 2--'20'
            when @ID1='M' then 11--'21'
            when @ID1='N' then 20--'22'
            when @ID1='P' then 29--'23'
            when @ID1='Q' then 38--'24'
            when @ID1='R' then 47--'25'
            when @ID1='S' then 56--'26'
            when @ID1='T' then 65--'27'
            when @ID1='U' then 74--'28'
            when @ID1='V' then 83--'29'
            when @ID1='X' then 3--'30'
            when @ID1='Y' then 12--'31'
            when @ID1='W' then 21--'32'
            when @ID1='Z' then 30--'33'
            when @ID1='I' then 39--'34'
            when @ID1='O' then 48--'35'
            else -1 end)
if(@Total=-1)
begin
 set @info=N'第一碼不是英文!';
end
else
begin
if(SUBSTRING(@ID,2,1) not in ('1','2'))
begin
 set @info=N'第二碼只能 1 或 2 !';
end
else
begin
if (ISNUMERIC(SUBSTRING(@ID,2,9))=0)
begin
 set @info=N'後九碼含非數字!';
end
else 
begin
--
while @i>0
begin
 set @Total=@Total+CAST(SUBSTRING(@ID,@j,1) AS int)*@i;
 set @j=@j+1;
 set @i=@i-1;
end
set @Total=@Total+CAST(SUBSTRING(@ID,@j,1) AS int);
set @Total=@Total%10;
if(@Total=0)
begin
 set @info='1';--驗證OK!
end
else
begin
 set @info=N'驗證失敗!';
end
end
end
end
end
return @info;
END

GO


 

arrow
arrow
    文章標籤
    驗證 SQL 身分證
    全站熱搜

    level168 發表在 痞客邦 留言(0) 人氣()