We have a table with 3 mill records. There is a field called Acctnumber in it.. I want to add leading 0s in front of the acct number such that for eg if acct num is 5678, I want to make to total len of 16 chars so it would be 0000000000005678, so have to pad 0s accordingly. The actual number can be any len ie 5678,2333333 etc: But total len should be 16. Pl advise.

Hello,

Add 16 "0" in front and limit the result with RIGHT function to 16 chars

SELECT RIGHT('0000000000000000' + Acctnumber, 16)
FROM yourTable

If Acctnumber is a numeric data type then you have to convert it first to type varchar

If it's a numeric field you will have to pad it on output. Otherwise you can store it in a text field in which case you can do this on input

SELECT RIGHT('0000000000000000'+ISNULL(field,''),16)

Hopefully this helps you get in the right direction, however this will create a new table.

SELECT myTable.*, Format([Field],"0000000000000000") AS NewFIeld INTO NewTable
FROM myTable;

• Proposed as answer by Naomi NModerator 11 hours 9 minutes ago

SELECT RIGHT(REPLICATE('0',16)+COALESCE(CAST(acctNumber AS VARCHAR(16)),''),16)
  FROM myTable

create table test (acctNumber  int)
insert into test values(123),(null),(987654321)
SELECT  Format(acctNumber,'0000000000000000') , len(Format(acctNumber,'0000000000000000'))
from test

drop table test

Try this in SQL SERVER:

Select Right(Replicate('0',16)+cast(546 as varchar(16)),16) aS Lpad

In Oracle we have function Lpad and Rpad that you can use

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

>> We have a table with 3 mill records [sic]. There is a field [sic] called Acctnumber in it.. <<

Rows are nothing like records. In SQL, a field is part of a temporal value {YEAR, MONTH, DAY, HOUR, MINUTE, SECOND}. These are basic terms  in the language. 

Your narrative sounds like  acct_nbr is a string. If you had followed the forum rules, would you have posted: 

 acct_nbr CHAR(16) NOT NULL 
 CHECK (acct_nbr 
 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),

This is one of many ways a column is not field it has constraints! 

>> I want to add leading 0s in front of the acct number such that for eg if acct-nbr is 5678, I want to make to total length of 16 chars so it would be 0000000000005678,  ..<<

NO! Your example violates your own business rule! Why are you trying to repair an error a competent SQL programmer would have prevented? 

>> The actual number can be any length ie 5678,2333333 etc: But total length should be 16. <<

Read that sentence; it makes no sense. Your mindset is still back in 1960's COBOL where we had to do crap like this in the old monolithic architecture,  with procedural code but even COBOL has a PICTURE clause.