“Format” a string in SQL Server query?

This is simple query from MS Access, how do i convert it to SQL Server 7.0 query? apparently SQL Server doesn’t support the function called Format.

SELECT Format(myNo, ‘000000’) as myNo From myTable

i’ve managed to come out with something which perform the same task in SQL Server, it looks like:

SELECT CONVERT(varchar(6), REPLICATE(‘0’, 6 – LEN(myNo))) + CONVERT(varchar(6), myNo) AS myNo FROM myTable

is this the correct way of doing so?

btw, can i create a user function SQL Server? i would like to make it a general function so that i can use it in other tables.

thanx