Go
--Create function (this function is from CSDN, the author is unknown)
Create function [dbo]. [Id15to18] (@id15 char (15))
Returns char (18)
As
Begin
DECLARE @id18 char (18)
Declare @s1 As Integer
Declare @s2 As Integer
Declare @s3 As Integer
Declare @s4 As Integer
Declare @s5 As Integer
Declare @s6 As Integer
Declare @s7 As Integer
Declare @s8 As Integer
Declare @s9 As Integer
Declare @s10 As Integer
Declare @s11 As Integer
Declare @s12 As Integer
Declare @s13 As Integer
Declare @s14 As Integer
Declare @s15 As Integer
Declare @s16 As Integer
Declare @s17 As Integer
Declare @s18 As Integer
Set @s1 =substring (@id15, 1, 1)
Set @s2 =substring (@id15, 2, 1)
Set @s3 =substring (@id15, 3, 1)
Set @s4 =substring (@id15, 4, 1)
Set @s5 =substring (@id15, 5, 1)
Set @s6 =substring (@id15, 6, 1)
Set @s7 =1
Set @s8 =9
Set @s9 =substring (@id15, 7, 1)
Set @s10 =substring (@id15, 8, 1)
Set @s11 =substring (@id15, 9, 1)
Set @s12 =substring (@id15, 10, 1)
Set @s13 =substring (@id15, 11, 1)
Set @s14 =substring (@id15, 12, 1)
Set @s15 =substring (@id15, 13, 1)
Set @s16 =substring (@id15, 14, 1)
Set @s17 =substring (@id15, 15, 1)
Set @s18 = ((@s1 *7) + (@s2 *9) + (@s3 *10) + (@s4) + (@s5 *8) + (@s6) + (@s7 *) + (@s8)
+ (@s9 *6) + (@s10) + (@s11 *7) + (@s12 *9) + (@s13 *10) + (@s14) + (@s15 *8) + (@s16
+ (@s17))%11
Set @id18 =substring (@id15, 1,6) + ' +substring ' (@id15, 7,9)
+case when @s18 =0 then ' 1 '
When @s18 =1 then ' 0 '
When @s18 =2 then ' x '
When @s18 =3 then ' 9 '
When @s18 =4 then ' 8 '
When @s18 =5 then ' 7 '
When @s18 =6 then ' 6 '
When @s18 =7 then ' 5 '
When @s18 =8 then ' 4 '
When @s18 =9 then ' 3 '
When @s18 =10 then ' 2 '
End
Return @id18
End
--Test example
SELECT [dbo]. [Id15to18] (' 222222830604321 ')
--Running results
/*
222222198306043213
*/
Upgrade your ID's 15-digit number to 18-bit