T-SQL use case -- auto-fill 0 before result data

Symptom:
In the development process, numbers and strings are often required to convert each other. In the conversion process, you may need to program 1 to 00001. There are many ways to achieve this. This article mainly provides a user-defined function to implement
 
Note:
Generally, CONVERT or CAST is used for this processing. The idea is to convert the string to a sufficient number of zeros first, and then use the RIGHT function to retrieve the required length from the RIGHT.
 
Case:
In this example, the AdventureWork database is used as an example.
First, execute the following statement:
-- Query Department data
SELECT distinct mentid, Name
FROM AdventureWorks. HumanResources. Department
 
The result is as follows:
 
Then, use CAST to convert the original department number to a VARCHAR () string. Add a fixed number of zeros, and then use the RIGHT function to remove the specified number of strings from the RIGHT side.
The Code is as follows:
-- Query the Department data and add the number to 0 based on the number of digits
Select right (REPLICATE ('0', 5) + CAST (reply mentid AS varchar (10), 5) AS 'reply mentid', Name
FROM AdventureWorks. HumanResources. Department
Order by dimension mentid
 
Result:

The above is the preparation work. Now you can compile a UDF to implement it. This function has two parameters. The first parameter is the number of zeros or other numbers to be supplemented, the second is the number to be converted. Finally, return the result as a string:
 
Function Code:
Create function fnZero
(
@ N INT,
@ I INT
)
Returns varchar (125)
AS
BEGIN
RETURN
(
RIGHT (REPLICATE ('0', @ n) + CAST (@ I as varchar (125), @ n)
)
END
 
Example:
-- Query the Department data and add the number to 0 based on the number of digits
SELECT primary mentid as 'Primary mentid', dbo. fnZero (5, primary mentid) as 'Primary mentid'
FROM AdventureWorks. HumanResources. Department
Order by 1
Note that because both columns have the same name, order by uses 1 instead of column name.
The result is as follows:
 
Of course, you can also improve the function so that the function can meet many requirements. Please study this part by yourself.
Author: DBA_Huangzj