Implement the padleft function in SQL Server

In. net, the right-aligned string of the string. padleft function can be used to fill the string with the specified UNICODE character on the left to reach the specified total length.
For example, auto-number is used.
Int nextid = da. getmaxnum () + 1;
This.txt code. Text = nextid. tostring (). padleft (6, '0 ');

Today, the customer wants to add a employee ID field to the user table. This field is automatically generated and can be easily implemented. However, there are more than 300 records in the User table, which must be numbered and manually entered.
Obviously, it is too troublesome, And the implementer will also feel boring. I just want to have a write segment script automatically numbered in the database, and found that the data does not provide functions similar to the right alignment.

So I checked it on the left and right, and wrote such a UDF.

-- Function: Right-aligned character, which is filled with the specified character on the left to reach the specified total length.
-- @ Num: original character @ paddingchar: Fill character @ totalwidth: number of characters in the result string
-- Create by Guoliang 2009/06/12

Create Function padleft (@ num varchar (16), @ paddingchar char (1), @ totalwidth INT)

Returns varchar (16)

Begin

Declare @ curstr varchar (16)

Select @ curstr = isnull (replicate (@ paddingchar, @ totalwidth-len (isnull (@ num, 0), '') + @ num

Return @ curstr

End

-- Test function:

Select DBO. padleft ('8', '0', 6)

----------------
000008

(The number of affected rows is 1)

Then, use the cursor to retrieve one row and modify the employee ID field.