how may I do this

concat(num1, num2) as account

assuming that num1 = 123 and num2 = 999, then account = 123999

no problem with above

if num = 000 then account becomes 999 and I want 000999

ie. concat(zerofill(num1), num2) as account

what is the correct method?

this works

concat(lpad(num1,3,0), num2) as account

Hi Susa,
If you really want to store all these zeros in the column I suggest you pad them upon insert. Otherwise you can pad them upon selecting. SQL Server does not have an automatic fill feature like this but you can do this very easily with code similar to this:

SELECT concat("000",num) as account;

Also,
SELECT LPAD(str,len,padstr);

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.
Example:
SELECT LPAD('000',6,'111');