根据身份证计算性别函数代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】根据身份证计算性别函数
作者:maco_wang / 发布于2012/2/8/ 641
<div>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">Go</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">--</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">创建函数</span><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">(</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">函数来自</span><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">csdn</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">，作者不详</span><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">)</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">create</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">function</span> [dbo]<span style="color: gray;">.</span>[Get_sex] </span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: gray; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">(</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;">    </span>@idcardno <span style="color: blue;">nvarchar</span><span style="color: gray;">(</span>50<span style="color: gray;">)</span></span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: gray; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">)</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">returns</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">int</span></span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">as</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">begin</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">declare</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> @sex <span style="color: blue;">int</span></span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">if</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: gray;">(</span><span style="color: fuchsia;">len</span><span style="color: gray;">(</span>@idcardno<span style="color: gray;">)=</span>18 <span style="color: gray;">and</span> <span style="color: fuchsia;">isnumeric</span><span style="color: gray;">(</span><span style="color: fuchsia;">substring</span><span style="color: gray;">(</span>@idcardno<span style="color: gray;">,</span>17<span style="color: gray;">,</span>1<span style="color: gray;">))=</span>1 <span style="color: gray;">)</span></span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">set</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> @sex<span style="color: gray;">=</span> <span style="color: gray;">(</span><span style="color: blue;">case</span> <span style="color: blue;">when</span> <span style="color: fuchsia;">substring</span><span style="color: gray;">(</span>@idcardno<span style="color: gray;">,</span>17<span style="color: gray;">,</span>1<span style="color: gray;">)</span><span style="mso-spacerun: yes;">  </span><span style="color: gray;">in(</span>1<span style="color: gray;">,</span>3<span style="color: gray;">,</span>5<span style="color: gray;">,</span>7<span style="color: gray;">,</span>9<span style="color: gray;">)</span> <span style="color: blue;">then</span> 1</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">when</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: fuchsia;">substring</span><span style="color: gray;">(</span>@idcardno<span style="color: gray;">,</span>17<span style="color: gray;">,</span>1<span style="color: gray;">)</span><span style="mso-spacerun: yes;">  </span><span style="color: gray;">in(</span>2<span style="color: gray;">,</span>4<span style="color: gray;">,</span>6<span style="color: gray;">,</span>7<span style="color: gray;">,</span>0<span style="color: gray;">)</span><span style="mso-spacerun: yes;">  </span><span style="color: blue;">then</span> 2 <span style="color: blue;">else</span> 0 <span style="color: blue;">end</span><span style="color: gray;">)</span></span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">else</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: blue;">if</span> <span style="color: gray;">(</span><span style="color: fuchsia;">len</span><span style="color: gray;">(</span>@idcardno<span style="color: gray;">)=</span>15 <span style="color: gray;">and</span> <span style="color: fuchsia;">isnumeric</span><span style="color: gray;">(</span><span style="color: fuchsia;">substring</span><span style="color: gray;">(</span>@idcardno<span style="color: gray;">,</span>15<span style="color: gray;">,</span>1<span style="color: gray;">))=</span>1 <span style="color: gray;">)</span></span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">set</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> @sex<span style="color: gray;">=</span> <span style="color: gray;">(</span><span style="color: blue;">case</span> <span style="color: blue;">when</span> <span style="color: fuchsia;">substring</span><span style="color: gray;">(</span>@idcardno<span style="color: gray;">,</span>15<span style="color: gray;">,</span>1<span style="color: gray;">)</span><span style="mso-spacerun: yes;">  </span><span style="color: gray;">in(</span>1<span style="color: gray;">,</span>3<span style="color: gray;">,</span>5<span style="color: gray;">,</span>7<span style="color: gray;">,</span>9<span style="color: gray;">)</span> <span style="color: blue;">then</span> 1</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">when</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> <span style="color: fuchsia;">substring</span><span style="color: gray;">(</span>@idcardno<span style="color: gray;">,</span>15<span style="color: gray;">,</span>1<span style="color: gray;">)</span><span style="mso-spacerun: yes;">  </span><span style="color: gray;">in(</span>2<span style="color: gray;">,</span>4<span style="color: gray;">,</span>6<span style="color: gray;">,</span>7<span style="color: gray;">,</span>0<span style="color: gray;">)</span><span style="mso-spacerun: yes;">  </span><span style="color: blue;">then</span> 2 <span style="color: blue;">else</span> 0 <span style="color: blue;">end</span><span style="color: gray;">)</span></span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">else</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> </span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">set</span> @sex<span style="color: gray;">=</span>0</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span><span style="color: blue;">return</span><span style="color: gray;">(</span>@sex<span style="color: gray;">)</span></span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">end</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> </span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">--</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">测试示例</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: blue; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">select</span><span lang="EN-US" style="font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> dbo<span style="color: gray;">.</span>[Get_sex]<span style="color: gray;">(</span><span style="color: red;">'222222198306043213'</span><span style="color: gray;">)</span></span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: gray; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;"> </span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">--</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">运行结果</span><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">(1</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">表示男</span><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">0</span><span style="color: green; font-family: 宋体; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes; mso-ascii-font-family: "Courier New"; mso-hansi-font-family: "Courier New"; mso-bidi-font-family: "Courier New";">表示女</span><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">)</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">/*</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">1</span>
<p align="left" class="MsoNormal" style="margin: 0cm 0cm 0pt; text-align: left; mso-layout-grid-align: none;"><span lang="EN-US" style="color: green; font-family: "Courier New"; font-size: 10pt; mso-font-kerning: 0pt; mso-no-proof: yes;">*/</span>
</div>
试试其它关键字
　身份证计算性别　
同语言下
可能有用的
maco_wang贡献的其它代码(36)