首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

更新数据-身份证解决方案

2012-02-29 
更新数据--------身份证gw_studentidcardsexbtime510132196401036236男196451011263111407351092219850207

更新数据--------身份证
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

热点排行