Formatting Numbers by padding with leading zeros in SQL Server

Question

We have an old SQL table that was used by SQL Server 2000 for close to 10 years   In it  our employee badge numbers are stored as char 6  from 000001 to 999999   I am writing a web application now  and I need to store employee badge numbers   In my new table  I could take the short cut and copy the old table  but I am hoping for better data transfer  smaller size  etc  by simply storing the int values from 1 to 999999   In C   I can quickly format an int value for the badge number using  public static string GetBadgeString int badgeNum      return string Format   0 000000    badgeNum        alternate      return string Format   0 d6    badgeNum       How would I modify this simple SQL query to format the returned value as well   SELECT EmployeeID FROM dbo RequestItems WHERE ID 0   If EmployeeID is 7135  this query should return 007135

User · Answer

Just use the FORMAT function  works on SQL Server 2012 or newer    SELECT FORMAT EmployeeID   000000   FROM dbo RequestItems WHERE ID 0    Reference  http   msdn microsoft com en-us library hh213505 aspx

User · Answer

The solution works for signed   negative numbers with leading zeros  for all Sql versions   DECLARE      n money   -3       length tinyint   15       decimals tinyint   0  SELECT REPLICATE  -   CHARINDEX  -    n  1     REPLACE REPLACE str  n   length   decimals    -              0

User · Answer

SELECT      cast replace str EmployeeID 6       0  as char 6    FROM dbo RequestItems WHERE ID 0

User · Answer

SELECT replicate  0   6 - len employeeID     convert varchar  employeeID  as employeeID FROM dbo RequestItems  WHERE ID 0

User · Answer

Hated having to CONVERT the int  and this seems much simpler  Might even perform better since there s only one string conversion and simple addition   select RIGHT 1000000   EmployeeId  6       Just make sure the  1000000  has at least as many zeros as the size needed

User · Answer

The simplest is always the best   Select EmployeeID 1 as EmployeeID

User · Answer

Another way  just for completeness   DECLARE  empNumber INT   7123 SELECT STUFF  000000   6-LEN  empNumber  1  LEN  empNumber    empNumber    Or  as per your query  SELECT STUFF  000000   6-LEN EmployeeID  1  LEN EmployeeID   EmployeeID            AS EmployeeCode FROM dbo RequestItems WHERE ID 0

User · Answer

I am posting all at one place  all works for me to pad with 4 leading zero     declare  number int    1  print right  0000    cast  number as varchar 4     4  print right  0000    convert varchar 4    number    4  print right replicate  0  4    convert varchar 4    number    4  print  cast replace str  number 4       0  as char 4   print format  number  0000

User · Answer

As clean as it could get and give scope of replacing with variables   Select RIGHT REPLICATE  0  6    EmployeeID  6  from dbo RequestItems WHERE ID 0

User · Answer

From version 2012 and on you can use  SELECT FORMAT EmployeeID  000000   FROM dbo RequestItems WHERE ID 0

User · Answer

In my version of SQL I can t use REPLICATE  SO I did this   SELECT      CONCAT REPEAT  0   6-LENGTH emplyeeID    emplyeeID  AS emplyeeID  FROM      dbo RequestItems

User · Answer

You can change your procedure in this way  SELECT Right  000000    CONVERT NVARCHAR  EmployeeID   6  AS EmpIDText          EmployeeID FROM dbo RequestItems  WHERE ID 0    However this assumes that your EmployeeID is a numeric value and this code change the result to a string  I suggest to add again the original numeric value  EDIT Of course I have not read carefully the question above  It says that the field is a char 6  so EmployeeID is not a numeric value  While this answer has still a value per se  it is not the correct answer to the question above

User · Answer

Change the number 6 to whatever your total length needs to be   SELECT REPLICATE  0  6-LEN EmployeeId     EmployeeId   If the column is an INT  you can use RTRIM to implicitly convert it to a VARCHAR   SELECT REPLICATE  0  6-LEN RTRIM EmployeeId      RTRIM EmployeeId    And the code to remove these 0s and get back the  real  number   SELECT RIGHT EmployeeId  LEN EmployeeId  - PATINDEX     0    EmployeeId     1

[sql] Formatting Numbers by padding with leading zeros in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to string-formatting