Formatting numbers using leading leading zeros in SQL Server

Question

We have an old SQL table that has been used by SQL Server 2000 for 10 years.

In it, our employee badge numbers are stored as char(6) from 000001 to 999999 .

Now I am writing a web application and I need to store employee badge numbers.

In my new table, I could make a short clipping and copy the old table, but I hope for better data transfer, smaller size, etc., just keeping int values ​​from 1 to 999999 .

In C #, I can quickly format the int value for the icon number using

 public static string GetBadgeString(int badgeNum) { return string.Format("{0:000000}", badgeNum); // alternate // return string.Format("{0:d6}", badgeNum); } 

How do I modify this simple SQL query to format the return value?

 SELECT EmployeeID FROM dbo.RequestItems WHERE ID=0 

If EmployeeID is 7135, this request should return 007135 .

Answer 1

Change number 6 to your entire total length:

 SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId 

If the column is INT, you can use RTRIM to implicitly convert it to VARCHAR

 SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId) 

And the code to remove these 0s and return a "real" number:

 SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1) 

Answer 2

Just use the FORMAT function (runs on SQL Server 2012 or later):

 SELECT FORMAT(EmployeeID, '000000') FROM dbo.RequestItems WHERE ID=0 

Link: http://msdn.microsoft.com/en-us/library/hh213505.aspx

Answer 3

You can change your procedure 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 changes the result to a string, I suggest adding the original numeric value again

EDIT Of course, I did not carefully read the question above. It indicates that the field is char(6) , so EmployeeID is not a numeric value. Although this answer still matters as such, this is not the correct answer to the question above.

Answer 4

I hate having to convert int, and it seems a lot easier. It may even work better, since there is only one string conversion and a simple append.

select LAW (1,000,000 + EmployeeId, 6) ...

Just make sure that "1,000,000" has at least as many zeros as necessary.

Answer 5

I place everything in one place, everything works for me to pave 4 leading zeros :)

 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') 

Answer 6

Another way, just for completeness.

 DECLARE @empNumber INT = 7123 SELECT STUFF('000000', 6-LEN(@empNumber)+1, LEN(@empNumber), @empNumber) 

Or as per your request

 SELECT STUFF('000000', 6-LEN(EmployeeID)+1, LEN(EmployeeID), EmployeeID) AS EmployeeCode FROM dbo.RequestItems WHERE ID=0 

Answer 7

How clean it is, how it could get, and provide an area of ​​substitution for variables:

 Select RIGHT(REPLICATE('0',6) + EmployeeID, 6) from dbo.RequestItems WHERE ID=0 

Answer 8

Starting from version 2012 and you can use

 SELECT FORMAT(EmployeeID,'000000') FROM dbo.RequestItems WHERE ID=0 

Answer 9

 SELECT replicate('0', 6 - len(employeeID)) + convert(varchar, employeeID) as employeeID FROM dbo.RequestItems WHERE ID=0 

Answer 10

 SELECT cast(replace(str(EmployeeID,6),' ','0')as char(6)) FROM dbo.RequestItems WHERE ID=0 

Answer 11

The solution works for signed / negative numbers with leading zeros for all versions of Sql:

 DECLARE @n money = -3, @length tinyint = 15, @decimals tinyint = 0 SELECT REPLICATE('-', CHARINDEX('-', @n, 1)) + REPLACE(REPLACE(str(@n, @length, @decimals), '-', ''), ' ', '0')