身份证,组织机构,营业执照号检验函数.在csdn上看到,感觉不错拿来一起分享了. go/****** object: userdefinedfunction [ro].[wj_validateidc] script date: 09/29/2012 11:23:34 ******/set ansi_nulls ongoset quoted_identifier ongo--验证身份证号码--编者：--创建日期：2012-09-18--参数：@idc 身份证号create function [ro].[wj_validateidc]( @idc varchar(18))returns bitasbegin 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 @itemp=@itemp+cast(substring(@idc,@i,1) as int)* (case substring(@validfactors,@i,1) when 'a' then 10 else substring(@validfactors,@i,1) end) ,@i=@i+1 end if substring(@validcodes,@itemp%11+1,1)=right(@idc,1) return 1 else return 0 return 0endgo/****** object: userdefinedfunction [ro].[wj_validateyyzzno] script date: 09/29/2012 11:25:49 ******/set ansi_nulls ongoset quoted_identifier ongo--功能：验证营业执照号--编者：--创建日期：2012-09-20--参数：@zzno 营业执照号create function [ro].[wj_validateyyzzno]( @zzno varchar(15))returns bitasbegin declare @m int, @n int, @s int , @p int ,@i int ,@result int select @m=10,@n=11,@p=@m 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 = @p+@s set @p =@p%@m if @p = 0 begin set @p= @m end set @p = @p *2 set @p = @p%@n set @i=@i+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 endreturn @resultendgo/****** object: userdefinedfunction [ro].[wj_validatezzjgno] script date: 09/29/2012 11:26:32 ******/set ansi_nulls ongoset quoted_identifier ongo--功能：组织机构号验证--编者：--创建日期：2012-09-20--参数：@zzjgno 组织机构号alter function [ro].[wj_validatezzjgno]( @zzjgno varchar(20))returns bitasbegindeclare @c varchar(20),@zz int,@z int,@i int,@jaz varchar(30),@c9 varchar(10)set @zzjgno = upper(@zzjgno)set @i=1while @i ='a' and @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)endelsebeginreturn 0end set @zz = isnull(@zz,0)+@z set @i = @i+1endset @jaz = 11-(@zz%11)if @jaz=10beginset @c9 = 'x'end else if @jaz =11beginset @c9='0' endelse beginset @c9=ltrim(rtrim(@jaz))end if @zzjgno = substring(@zzjgno,1,8) +@c9begin return 1 end if @zzjgno = substring(@zzjgno,1,8)+'-'+@c9begin return 1endif @zzjgno = substring(@zzjgno,1,8) + '—' +@c9begin return 1endif @zzjgno = substring(@zzjgno,1,8) +' '+@c9begin return 1 end return 0end