Generate a sequential number of a specified length in SQL Server

In many cases, we will use a sequential number with a specified length. If we generate an order encoding rule, we will set it to Warehouse encoding + year, month, and day (6 digits) + four sequential numbers for the current day, for example, the first order code of the warehouse code of 01 to should be: 01200908060001. Here, the generation of the serial number with the specified length is generally performed in the database. You can use the following methods:

Method 1: Stupid. If the length is not specified enough, add zero to the previous loop.

Create Function Fn_generateserialnumber
(
@ Numbervalue Int , -- Flow number value
@ Length Int -- Length specified by the serial number string
)
Returns Varchar ( 20 )
As
Begin
Declare @ Result Varchar (20 )
Set @ Result = Cast ( @ Numbervalue As Varchar )

Declare @ Currentlen Int
Set @ Currentlen = Len ( @ Result )

While ( @ Currentlen < @ Length )
Begin
Set @ Result = ' 0 ' + @ Result
Set @ Currentlen = @ Currentlen + 1
End

Return @ Result
End Method 2: Use Replace + Str Function

Create Function Fn_generateserialnumber2
(
@ Numbervalue Int , -- Flow number value
@ Length Int -- Length specified by the serial number string
)
Returns Varchar ( 20 )
As
Begin
Return Replace ( Str ( @ Numbervalue , @ Length ), '   ' , ' 0 ' )
End Method 3: Use Replicate Function

Create Function Fn_generateserialnumber3
(
@ Numbervalue Int , -- Flow number value
@ Length Int -- Length specified by the serial number string
)
Returns Varchar ( 20 )
As
Begin
Declare @ Result Varchar (20 )
Set @ Result = Cast ( @ Numbervalue As Varchar )
Set @ Result = Replicate ( ' 0 ' , @ Length - Len ( @ Result )) + @ Result

Return @ Result
End Method 4: Use Right + replicate Function

Create Function Fn_generateserialnumber4
(
@ Numbervalue Int , -- Flow number value
@ Length Int -- Length specified by the serial number string
)
Returns Varchar ( 20 )
As
Begin
Declare @ Result Varchar ( 20 )
Set @ Result = Cast ( @ Numbervalue As Varchar )
Set @ Result = Right ( Replicate (' 0 ' , @ Length ) + @ Result , @ Length )

Return @ Result
End

 

In addition, for Method 4, you can also consider

Set @ Result = Reverse(Substring(Reverse(@ Result)+ Replicate('0',@ Length),1,@ Length))

There are many methods, such as string function combinationsNYes.