Formatting Numbers by padding with leading zeros in SQL Server

by

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)

More Related Contents:

  1. SQL query for display one field only once which having multiple record
  2. T-SQL split string
  3. Exclude a column using SELECT * [except columnA] FROM tableA?
  4. Conditional WHERE clause in SQL Server
  5. How do I create a comma-separated list using a SQL query?
  6. How to Join to first row
  7. Concatenate row values T-SQL
  8. Multi-statement Table Valued Function vs Inline Table Valued Function
  9. How to return multiple values in one column (T-SQL)?
  10. OR is not supported with CASE Statement in SQL Server
  11. Parse comma-separated string to make IN List of strings in the Where clause
  12. How to set variable from a SQL query?
  13. SELECT FOR UPDATE with SQL Server
  14. T-SQL: Selecting Column Based on MAX(Other Column)
  15. How to group by month from Date field using sql
  16. unresolved reference to object [INFORMATION_SCHEMA].[TABLES]
  17. SQL dot notation
  18. Split function in SQL Server 2008
  19. T-SQL How to create tables dynamically in stored procedures?
  20. Curly braces in T-SQL
  21. OPENXML with xmlns:dt
  22. Find min and max for subsets of consecutive rows – gaps and islands
  23. How can I group by date time column without taking time into consideration
  24. Database Naming Conventions by Microsoft?
  25. Convert date to YYYYMM format
  26. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  27. Is there a quick way to check if ANY column is NULL?
  28. SQL query for parent-child chain
  29. T-SQL Conditional Order By
  30. How to group by a Calculated Field

Leave a Comment