身份证升位和中英文转化
2013年01月25日
⁄ 综合
⁄ 共 4441字 ⁄ 字号
小 中 大
declare @i as int
declare @j as int
declare @S as int
declare @NewPersonID As char(20)
declare @PersonID as char(50)
SET @PersonID='210103530204152'
--去掉空格：
set @PersonID = rtrim(ltrim(@PersonID))
--如果不是数字,就返回原来的字符 :
If IsNumeric(@PersonID) = 0 or len(@PersonID) <> 15
begin
select @PersonID
End
set @NewPersonID = SUBSTRING(@PersonID, 1, 6) + '19' + SUBSTRING(@PersonID, 7,9)
set @S = 0
set @i=1
--Dim w() As Integer = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1}
while @i<=17
begin
if @i - 1=0
SET @j = Cast(SUBSTRING(@NewPersonID,@i, 1) AS int) * 7
else if @i - 1=1
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 9
else if @i - 1=2
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 10
else if @i - 1=3
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 5
else if @i - 1=4
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 8
else if @i - 1=5
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 4
else if @i - 1=6
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 2
else if @i - 1=7
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 1
else if @i - 1=8
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 6
else if @i - 1=9
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 3
else if @i - 1=10
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 7
else if @i - 1=11
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 9
else if @i - 1=12
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 10
else if @i - 1=13
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 5
else if @i - 1=14
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 8
else if @i - 1=15
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 4
else if @i - 1=16
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 2
else if @i - 1=17
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 1
set @S = @S + @j
set @i= @i+1
end
-- Dim a() As String = {"1", " 0", "x", "9", "8", "7", "6", "5", "4", "3", "2"}
set @S = @S % 11
if @S=0
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + '1'
else if @S=1
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + '0'
else if @S=2
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + 'x'
else if @S=3
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + '9'
else if @S=4
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + '8'
else if @S=5
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + '7'
else if @S=6
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + '6'
else if @S=7
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + '5'
else if @S=8
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + '4'
else if @S=9
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + '3'
else if @S=10
select ltrim(rtrim(cast(@NewPersonID as char(20)))) + '2'
‘————————————————————————————————————————————————-
'函数：
CREATE FUNCTION [dbo].[PersonID15To18] (@PersonID char(50))
RETURNS char(20) AS
BEGIN
declare @i as int
declare @j as int
declare @S as int
declare @NewPersonID As char(20)
-- SET @PersonID='210103530204152'
--去掉空格：
set @PersonID = rtrim(ltrim(@PersonID))
--如果不是数字,就返回原来的字符 :
If IsNumeric(@PersonID) = 0 or len(@PersonID) <> 15
begin
return @PersonID
End
set @NewPersonID = SUBSTRING(@PersonID, 1, 6) + '19' + SUBSTRING(@PersonID, 7,9)
set @S = 0
set @i=1
--Dim w() As Integer = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1}
while @i<=17
begin
if @i - 1=0
SET @j = Cast(SUBSTRING(@NewPersonID,@i, 1) AS int) * 7
else if @i - 1=1
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 9
else if @i - 1=2
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 10
else if @i - 1=3
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 5
else if @i - 1=4
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 8
else if @i - 1=5
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 4
else if @i - 1=6
SET @j = Cast(SUBSTRING(@NewPersonID, @i, 1) AS int) * 2
else if @i - 1=7