Upgrade your ID's 15-digit number to 18-bit

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.