gw_student
idcard sex btime
510132196401036236 男 1964
510112631114073
510922198502073570
求一条语句
用来更新添加身份证的sex(性别)和btime(出生年)
------解决方案--------------------
declare @t table(
idcard varchar(18),
sex varchar(10),
btime int)
insert @t select '510132196401036236 ', '男 ', 1964
union all select '510112631114073 ', null, null
union all select '510922198502073570 ', null, null
select idcard,
sex = case len(idcard)
when 15 then case (cast(right(idcard, 1) as int) % 2)
when 1 then '男 '
else '女 '
end
else case (cast(substring(idcard, 17, 1) as int) % 2)
when 1 then '男 '
else '女 '
end
end,
btime = case len(idcard)
when 15 then cast( '19 ' + substring(idcard, 7, 2) as int)
else cast(substring(idcard, 7, 4) as int)
end
from @t
/*
idcard sex btime
------------------ ---- -----------
510132196401036236 男 1964
510112631114073 男 1963
510922198502073570 男 1985
（所影响的行数为 3 行）
*/
------解决方案--------------------
create table gw_student(idcard varchar(18),sex varchar(10),btime varchar(4))
insert gw_student select '510132196401036236 ', '男 ', 1964
union all select '510112631114073 ', null, null
union all select '510922198502073570 ', null, null
update gw_student
set sex=case len(idcard)
when 15 then case (cast(right(idcard, 1) as int) % 2)
when 1 then '男 '
else '女 '
end
else case (cast(substring(idcard, 17, 1) as int) % 2)
when 1 then '男 '
else '女 '
end
end,
btime=case len(idcard)
when 15 then '19 '+substring(idcard, 7, 2)
else substring(idcard, 7, 4)
end
select * from gw_student
drop table gw_student