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
(
@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
