Because in the process of using, found this requirement, and then understand the operation of SQL Server, so make a record here.

We use the RIGHT() function in SQL Server. The RIGHT() function is expressed as:

RIGHT(character_expression,integer_expression)Copy the code

In the preceding command, character_expression indicates a string expression, and integer_expression indicates the returned length. In this case, we use the length limit of 5 as an example.

The original data is as follows:



Next we format the sp_code field using the RIGHT() function:

The first way to write:

RIGHT('00000' + sp_code, 5)Copy the code



Second way to write:

RIGHT('00000' + CONVERT(VARCHAR, sp_code), 5)Copy the code

Note: CONVERT() is a generic function in SQL Server to CONVERT a date to a new data type. Here we use

CONVERT(VARCHAR, sp_code)Copy the code

To convert sp_code to a character type.



The third way to write:

RIGHT('00000' + LTRIM(RTRIM(sp_code)), 5)Copy the code

Note: LTRIM() and RTRIM() are SQL Server functions that remove whitespace from data



The fourth way to write:

RIGHT('00000' + CASE(sp_code AS varchar), 5)Copy the code




Fifth way to write:

RIGHT(replicate('0', 5) + sp_code, 5)Copy the code

Note: REPLICATE() is a function for replicating a character expression in SQL Server a specified number of times.




Sixth way to write:

RIGHT(100000 + sp_code, 5)Copy the code




To sum up, there are many ways to write, here are only a few of them.