Author:水如烟
SQLServer2005 身份证函数,含验证和15位转18位
USE
[
LzmTWWorks
]
GO
/* ***** 对象: UserDefinedFunction [Helper].[IDCard] 脚本日期: 12/07/2007 23:21:37 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ Helper ] . [ IDCard ]
(
@Card varchar ( 18 )
)
RETURNS
@TCard TABLE
(
Input varchar ( 18 )
,IDCard varchar ( 18 )
,Valid bit
)
AS
BEGIN
DECLARE
@Input as varchar ( 18 )
, @IDCard as varchar ( 18 )
, @Valid as bit
DECLARE
@Length as smallint
, @TmpCard as varchar ( 18 )
, @IsOld as bit
SET @Valid = 0
SET @IDCard = ''
SET @Input = ''
IF @Card IS NULL GOTO Finish
SET @Input = LTRIM ( RTRIM ( @Card )) /* 去空格 */
SET @Length = LEN ( @Input )
IF NOT @Length IN ( 15 , 18 ) GOTO Finish /* 非15、18位 */
IF @Length = 15
BEGIN
IF ISNUMERIC ( @Input ) = 0 GOTO Finish /* 非数字 */
SET @TmpCard = LEFT ( @Input , 6 ) + ' 19 ' + RIGHT ( @input , 9 ) /* 补充为17位 */
SET @IsOld = 1
END
ELSE
BEGIN
IF ISNUMERIC ( LEFT ( @Input , 17 )) = 0 GOTO Finish /* 非数字 */
SET @TmpCard = LEFT ( @Input , 17 ) /* 取前17位 */
SET @IsOld = 0
END
DECLARE @Birthday varchar ( 8 )
SET @Birthday = SUBSTRING ( @TmpCard , 7 , 8 )
IF ISDATE ( @birthday ) = 0 GOTO Finish /* 非日期 */
-- 前17位数与相应加权因子的积的和
DECLARE
@Sum as smallint
, @WI as tinyint
, @Index as tinyint
, @Num as tinyint
SET @Sum = 0
SET @Index = 1
GO
/* ***** 对象: UserDefinedFunction [Helper].[IDCard] 脚本日期: 12/07/2007 23:21:37 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ Helper ] . [ IDCard ]
(
@Card varchar ( 18 )
)
RETURNS
@TCard TABLE
(
Input varchar ( 18 )
,IDCard varchar ( 18 )
,Valid bit
)
AS
BEGIN
DECLARE
@Input as varchar ( 18 )
, @IDCard as varchar ( 18 )
, @Valid as bit
DECLARE
@Length as smallint
, @TmpCard as varchar ( 18 )
, @IsOld as bit
SET @Valid = 0
SET @IDCard = ''
SET @Input = ''
IF @Card IS NULL GOTO Finish
SET @Input = LTRIM ( RTRIM ( @Card )) /* 去空格 */
SET @Length = LEN ( @Input )
IF NOT @Length IN ( 15 , 18 ) GOTO Finish /* 非15、18位 */
IF @Length = 15
BEGIN
IF ISNUMERIC ( @Input ) = 0 GOTO Finish /* 非数字 */
SET @TmpCard = LEFT ( @Input , 6 ) + ' 19 ' + RIGHT ( @input , 9 ) /* 补充为17位 */
SET @IsOld = 1
END
ELSE
BEGIN
IF ISNUMERIC ( LEFT ( @Input , 17 )) = 0 GOTO Finish /* 非数字 */
SET @TmpCard = LEFT ( @Input , 17 ) /* 取前17位 */
SET @IsOld = 0
END
DECLARE @Birthday varchar ( 8 )
SET @Birthday = SUBSTRING ( @TmpCard , 7 , 8 )
IF ISDATE ( @birthday ) = 0 GOTO Finish /* 非日期 */
-- 前17位数与相应加权因子的积的和
DECLARE
@Sum as smallint
, @WI as tinyint
, @Index as tinyint
, @Num as tinyint
SET @Sum = 0
SET @Index = 1