用SQL函数判断是否有效18位身份证号

本文介绍了一个SQL Server存储过程,用于验证18位中国身份证号码的有效性。该过程检查身份证号长度、出生年份标识、全部字符为数字,并通过特定算法验证校验位。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

ALTER  FUNCTION CheckSNID(@snid nvarchar(50))
RETURNS  bit  AS
BEGIN
  declare @iRet bit
  declare @id_num varchar(1)
  declare @i int
  declare @sn_sum int
  declare @sn_Last varchar(1)
  set @iRet=0
  --判断是不是18位 
  if (len(@snid)<> 18) or (isnull(@snid,'')='')
  goto ext
  --第七位、第八位不是19 或者 20
  if not ((substring(@snid,7,2)='19')  or (substring(@snid,7,2)='20'))
  goto ext
  --判断前17位是否都是数字
  select @i=1,@id_num='',@sn_sum=0,@sn_Last=''
  while @i<18
  begin
    --截取身份证中的一位
    set @id_num=substring(@snid,@i,1)
    if (@id_num<'0') or (@id_num>'9')
    goto ext
    select @sn_sum=(
    case @i when 1 then @sn_sum+cast(@id_num as int)*7 
    when 2 then @sn_sum+cast(@id_num as int)*9
    when 3 then @sn_sum+cast(@id_num as int)*10
    when 4 then @sn_sum+cast(@id_num as int)*5
    when 5 then @sn_sum+cast(@id_num as int)*8
    when 6 then @sn_sum+cast(@id_num as int)*4
    when 7 then @sn_sum+cast(@id_num as int)*2
    when 8 then @sn_sum+cast(@id_num as int)*1
    when 9 then @sn_sum+cast(@id_num as int)*6
    when 10 then @sn_sum+cast(@id_num as int)*3
    when 11 then @sn_sum+cast(@id_num as int)*7
    when 12 then @sn_sum+cast(@id_num as int)*9
    when 13 then @sn_sum+cast(@id_num as int)*10
    when 14 then @sn_sum+cast(@id_num as int)*5
    when 15 then @sn_sum+cast(@id_num as int)*8
    when 16 then @sn_sum+cast(@id_num as int)*4
    when 17 then @sn_sum+cast(@id_num as int)*2 end)
    set @i=@i+1 
  end
  --根据取余判断最后位
  set @sn_sum=@sn_sum%11
  select @sn_Last=
  (case @sn_sum when 0 then '1'
  when 1 then '0' 
  when 2 then 'X'
  when 3 then '9'
  when 4 then '8'
  when 5 then '7'
  when 6 then '6'
  when 7 then '5'
  when 8 then '4'
  when 9 then '3'
  when 10 then '2' end)
  if (@sn_Last='X')
  BEGIN 
   if (substring(@snid,18,1)='X') or (substring(@snid,18,1)='x')
     set @iRet=1
  END
  ELSE
    if (@sn_Last=substring(@snid,18,1))
      set @iRet=1
  ext:
  return @iRet
END;
<think>首先,用户的问题是如何通过SQL语句从身份证号中提取出生日期。身份证号在中国通常是18位数字,其中第7到14代表出生日期,格式为YYYYMMDD。 我需要参考系统级指令: - 所有行内数学表达式用$...$,但这里可能不需要数学表达式。 - 独立公式用$$...$$,但这个问题可能不涉及复杂公式。 - LaTeX语法正确,但在这个上下文中,SQL代码更重要。 - 使用中文回答。 - 生成相关问题。 - 回答中引用的段落末尾添加引用标识。引用来自提供的参考,但用户说“上述引用仅供参考”,所以我应该基于问题本身回答,但可以适当参考引用。 用户提供的引用: - [^1]: 创建表tb_person_info,有person_birth字段。 - [^2]: MySQL查询,包括limit和日期计算。 - [^3]: Hive SQL导入导出。 - [^4]: SQL开窗函数。 用户的问题是提取出生日期从身份证号,所以我需要聚焦在SQL字符串操作和日期提取。 核心步骤: 1. 身份证号通常是字符串,所以用SQL的字符串函数提取子串。 2. 提取第7到14,这表示出生日期。 3. 可能转换为日期类型,以便存储或使用。 在SQL中,常见函数: - SUBSTRING 或 SUBSTR 来提取子串。 - 然后可能用CAST或CONVERT来转换为日期格式。 参考引用[^1]中有person_birth字段是date类型,所以提取后应存储为date。 引用[^2]有日期计算函数,如TIMESTAMPdiff,可能相关。 引用[^3]和[^4]可能不那么直接相关,但[^4]有开窗函数,不直接用于此。 现在,构建回答: - 结构清晰:逐步解释。 - 使用中文。 - 在末尾生成相关问题。 假设身份证号存储在表中,比如在tb_person_info的某个字段,但用户没有指定表名。所以,我需通用化。 在引用[^1]中,表是tb_person_info,有person_id等字段。假设身份证号字段叫id_card或类似。 用户说“我想通过SQL语句从身份证号中提取出生日期”,所以我可以假设有一个表有身份证号字段。 提取步骤: 1. 使用SUBSTRING函数提取出生日期部分。 - 例如,SUBSTRING(id_card, 7, 8) 提取从第7开始的8个字符。 2. 然后,转换为日期类型。 - 在MySQL中,可以用STR_TO_DATE函数将字符串转换为日期。 - 格式为'YYYYMMDD'。 所以,SQL语句可能像: SELECT STR_TO_DATE(SUBSTRING(id_card, 7, 8), '%Y%m%d') AS birth_date FROM table_name; 在Hive或其它SQL方言中,函数可能不同,但用户提到MySQL在引用[^2],所以聚焦MySQL。 引用[^2]是MySQL入门资料,有curdate(), now(), TIMESTAMPdiff等。 所以,使用MySQL语法。 现在,完整SQL示例。 也,考虑身份证号可能无效,但用户没说处理错误,所以假设有效。 在回答中,添加引用标识。例如,引用[^1]有表结构,引用[^2]有日期函数。 但用户说“上述引用仅供参考”,所以我不能强制引用,但可以自然地提及。 生成相关问题: - 例如: 1. 如何验证身份证号的合法性? 2. 如何处理身份证号中的性别信息? 3. 在SQL中如何计算年龄从出生日期? 类似示例中的快速排序问题。 回答结构: - 先解释方法。 - 给出SQL代码示例。 - 然后相关问题。</think>### 从身份证号中提取出生日期的SQL方法 在中国,身份证号18)的第7到14代表出生日期,格式为`YYYYMMDD`(例如,`19900101`表示1990年1月1日)。通过SQL提取出生日期,主要涉及字符串操作和日期转换。以下是基于MySQL的逐步实现方法(其他SQL方言如Hive或PostgreSQL语法类似,但函数名可能不同)。假设身份证号存储在表的字段中,例如引用[^1]中的`tb_person_info`表,您可以添加一个`id_card`字段来存储身份证号。 #### 步骤详解 1. **提取日期子串**:使用`SUBSTRING()`函数截取身份证号的第7到14(索引从1开始)。 - 语法:`SUBSTRING(id_card, 7, 8)`,其中`id_card`是身份证号字段名,`7`是起始置,`8`是长度。 2. **转换为日期类型**:将提取的字符串(如`'19900101'`)转换为SQL的`DATE`类型,以便存储或计算。在MySQL中,使用`STR_TO_DATE()`函数,指定格式`'%Y%m%d'`(`%Y`表示4年份,`%m`表示月份,`%d`表示日期)。 - 引用[^2]中提到了日期计算函数(如`TIMESTAMPdiff()`),转换后可直接用于类似年龄计算等操作。 3. **完整SQL示例**: - 假设表结构如引用[^1]:`tb_person_info`包含`id_card`字段(需先添加此字段)。 - 提取出生日期并更新到`person_birth`字段(或直接查询): ```sql -- 查询出生日期(不修改表) SELECT person_id, person_name, STR_TO_DATE(SUBSTRING(id_card, 7, 8), '%Y%m%d') AS birth_date FROM tb_person_info; -- 更新表,将出生日期存储到person_birth字段 UPDATE tb_person_info SET person_birth = STR_TO_DATE(SUBSTRING(id_card, 7, 8), '%Y%m%d'); ``` - **说明**: - `SUBSTRING(id_card, 7, 8)`:提取8日期字符串。 - `STR_TO_DATE(..., '%Y%m%d')`:将字符串转为`DATE`类型(例如`'19900101'`转为`1990-01-01`)。 - 如果身份证号无效(如长度不足),SQL可能返回`NULL`;建议添加校验(如`LENGTH(id_card) = 18`),但这超出本问题范围。 #### 注意事项 - **性能优化**:在大表上操作时,先添加索引到`id_card`字段以提高速度[^1]。 - **跨数据库兼容**: - 在Hive(引用[^3])中,使用`SUBSTR()`和`TO_DATE()`函数。 - 在SQL Server中,用`CONVERT(DATE, SUBSTRING(id_card, 7, 8))`。 - **实际应用**:提取后,可结合引用[^2]中的日期函数(如`TIMESTAMPdiff()`)计算年龄: ```sql SELECT person_name, birth_date, TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age -- 计算当前年龄 FROM ( SELECT person_name, STR_TO_DATE(SUBSTRING(id_card, 7, 8), '%Y%m%d') AS birth_date FROM tb_person_info ) AS t; ``` ####
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值