update [表名] set [性别]=(case when (left(right([身份证],2),1) % 2 = 0) then '女' else '男' end ) where len([身份证])=18 and left(right([身份证],2),1)<>'X' update [表名] set [性别]=(case when (right([身份证],1) % 2 = 0) then '女' else '男' end ) where len([身份证])=15 and right([身份证],1)<>'X' update [表名] set [出生日期]=(right(left([身份证],10),4)+'-'+right(left([身份证],12),2)+'-'+right(left([身份证],14),2)) where len([身份证])=18 update [表名] set [出生日期]=('19'+right(left([身份证],8),2)+'-'+right(left([身份证],10),2)+'-'+right(left([身份证],12),2)) where len([身份证])=15 update [表名] set [年龄]=year(getdate())-(cast(left([出生日期],4) as int)) where [出生日期]<>''
#字符串格式化为日期格式 SELECT DATE_FORMAT('19841201','%Y-%m-%d') #---知道出生日期求年龄 SELECT TIMESTAMPDIFF(YEAR,DATE_FORMAT('19841201','%Y-%m-%d'),CURDATE()); #截取身份证号中的年月日 SELECT SUBSTR('152824197810116015',7,8); SELECT SUBSTR('152824781011601',7,6) #连接字符串 SELECT CONCAT('33','22') #截取身份证号中的年月日 1 SELECT IF (LENGTH('152824197810116015')=18, SUBSTR('152824197810116015',7,8), CONCAT('19',SUBSTR('152824197810116015',7,6)) ); #截取身份证号中的年月日 2 SELECT IF (LENGTH('152824781011601')=18, SUBSTR('152824781011601',7,8), CONCAT('19',SUBSTR('152824781011601',7,6)) ); #根据身份证号计算年龄 SELECT TIMESTAMPDIFF(YEAR, DATE_FORMAT( IF (LENGTH('152824781011601')=18, SUBSTR('152824781011601',7,8), CONCAT('19',SUBSTR('152824781011601',7,6)) ),'%Y-%m-%d'), CURDATE());
update @T set nl = case when len(ltrim(rtrim(sfz))) = 18 then cast(Year(GetDate()) as int)- cast(substring(ltrim(rtrim(sfz)),7,4) as int) else cast(Year(GetDate()) as int)- cast('19'+substring(ltrim(rtrim(sfz)),7,2) as int) end