T-SQL自定义函数：从身份证提取信息

CREATE FUNCTION dbo.GetInfoFromIDCard
(
  @IDCard VARCHAR(18),
  @InfoType VARCHAR(20)
)
RETURNS NVARCHAR(20)
AS 
BEGIN
      DECLARE @Ret NVARCHAR(20)
      SELECT
            @Ret = CASE @InfoType
                    --TODO:根据身份证1~6位判断出户籍所在地
                    --WHEN 'Region' THEN
                     WHEN 'Birthday'
                     THEN CASE LEN(LTRIM(RTRIM(@IDCard)))
                            --如果身份证为15位
                            --身份证7~12代表出生日期19XX-XX-XX
                            WHEN 15
                            THEN '19' + SUBSTRING(@IDCard, 7, 2)
                                 + '-' + RIGHT('00' + SUBSTRING(@IDCard, 9, 2), 2)
                                 + '-' + RIGHT('00' + SUBSTRING(@IDCard, 11, 2), 2)
                            --如果身份证为18位
                            --身份证7~14代表出生日期XXXX-XX-XX
                            WHEN 18
                            THEN
                                 SUBSTRING(@IDCard, 7, 4)
                                 + '-' + RIGHT('00' + SUBSTRING(@IDCard, 11, 2), 2)
                                 + '-' + RIGHT('00' + SUBSTRING(@IDCard, 13, 2), 2)
                            ELSE NULL
                          END
                     WHEN 'Gender'
                     THEN CASE LEN(LTRIM(RTRIM(@IDCard)))
                            --如果身份证为15位
                            --倒数第一位数为奇数则为男,偶数则为女
                            WHEN 15
                            THEN CASE CAST(RIGHT(@IDCard, 1) AS INT)
                                   % 2
                                   WHEN 1 THEN N'M'
                                   ELSE N'F'
                                 END
                            --如果身份证为18位
                            --倒数第二位数为奇数则为男,偶数则为女
                            WHEN 18 THEN
                                 CASE CAST(LEFT(RIGHT(@IDCard, 2), 1) AS INT)
                                   % 2
                                   WHEN 1 THEN N'M'
                                   ELSE N'F'
                                 END
                            ELSE NULL
                          END
                   END
      WHERE
            --判断身份证7~14位的数字是否为日期类型
            ISDATE(CASE LEN(LTRIM(RTRIM(@IDCard)))
                     WHEN 15
                     THEN '19' + SUBSTRING(@IDCard, 7, 2)
                          + '-' + RIGHT('00'
                                        + SUBSTRING(@IDCard, 9, 2),
                                        2) + '-'
                          + RIGHT('00' + SUBSTRING(@IDCard, 11, 2),
                                  2)
                     WHEN 18
                     THEN SUBSTRING(@IDCard, 7, 4) + '-'
                          + RIGHT('00' + SUBSTRING(@IDCard, 11, 2),
                                  2) + '-' + RIGHT('00' + SUBSTRING(@IDCard, 13, 2), 2)
                   END) = 1
            --判断身份证位数是15位或者18位
            AND (
            LEN(LTRIM(RTRIM(@IDCard))) = 15
            OR LEN(LTRIM(RTRIM(@IDCard))) = 18
            )
      RETURN @Ret
END