178

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.

Improve this question
8
  • Since leading 0s are significant to this field, why change it to an INT at all? Commented Mar 1, 2012 at 17:09
  • 3
    SQL Server 2012 will finally have a FORMAT function like C# :-) Commented Mar 1, 2012 at 17:10
  • 1
    @Oden: The int values take up significantly less space that char(6), and there will be multiple of these entries per part that gets manufactured. Efficiency. Commented Mar 1, 2012 at 17:14
  • Are you optimizing before you are experiencing a problem? Commented Mar 1, 2012 at 17:17
  • 6
    Let's see, you have up to one million badge numbers and you figure that you can save (according to DATALENGTH()) two bytes each. Since the column might be in an index, you could be saving more than 2MB. And with other columns added up, that 2 bytes might be just enough to reduce the length of a row enough to save a 4KB page per row. Is this going to be hosted on a mobile platform, or might you be focusing your attention in an unproductive area? Commented Mar 1, 2012 at 18:09

15 Answers 15

Reset to default
234

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

+1 You even showed me how to remove the 0s! Let me test this, and I'll mark it as an answer.
you don't need code to remove the zeroes, just assign or convert to an int and they will be removed automatically.
This only works if the value passed in is a string, if you pass an integer you get out the same value you passed in.
Though its old... using "+ convert (varchar, EmployeeID)" instead of "+ EmployeeID" should solve the int issue
it is a good way unless the EmployeeId be greater than 6 digits which causes NULL result. The contact function is the answer: SELECT CONCAT(REPLICATE('0',6-LEN(CONVERT(varchar,EmployeeId))) , EmployeeId)
|
227

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

Improve this answer

7 Comments

This is an old question - before SQL Server 2012 came out.
Still, I'd like to see this bubble a little closer to the top now.
Just in case anybody is wondering. Format does not preserve the data type but implicitly converts to nvarchar: select sql_variant_property(50, 'BaseType'), sql_variant_property(format(50, N'00000'), 'BaseType')
This way is the simplest and IMHO the best solution.
FORMAT in SQL Server is still implemented by calling into .NET's IFormattable API which adds a significant performance penalty when called from T-SQL. Consider using the REPLICATE approach from stackoverflow.com/a/9520709/159145 instead. Here's an article from 2015 about FORMAT's poor performance: sqlperformance.com/2015/06/t-sql-queries/…
|
38

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.

Improve this answer

4 Comments

This is the cleanest way of doing it I think. Thanks
Is that '000000' really necessary..? '0' also working fine. Is it safe to use just one 0 ..?
The OP asked for a 6 chars length string as result. Specifically if EmployeeID is 7135, this query should return 007135. Using only one '0' returns 07135 not 007135. The whole idea is to concatenate to a 6 char string composed of all 0 the string converted EmployeedID, then STARTING from the right edge take 6 chars. Whatever length the ID is the method above returns always a string with just the number of zero char required to reach the 6 char length
It could be 5 zeroes '00000' since the EmployeeID will contain at least one digit. But the REPLICATE() function seems more fit, like in the other answers
28

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.

Improve this answer

Comments

16

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')
Improve this answer

1 Comment

This covers all needed options. Thank you.
10

From version 2012 and on you can use

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

1 Comment

Straight forward approach! I needed to get hour and minute from a date, so I combined concat, format and datepart. CONCAT_WS(':',FORMAT(datepart(hh, [Dt].[StartTime]),'00') ,FORMAT(datepart(mi, [Dt].[StartTime]),'00')) AS StartTime
7

To account for negative numbers without overflowing 6 characters...

FORMAT(EmployeeID, '000000;-00000')
Improve this answer

Comments

6

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
Improve this answer

1 Comment

@jp2code - you need to read up on what StackOverflow is - its collaboratively edited like a wiki - its not your question as soon as you've posted it! Things that are commonly edited out are excessive "fluff" like thanking in advance and poor grammar/capitalisation.
4

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
Improve this answer

2 Comments

If the EmployeeID column is defined as int then the + operator will be treated as addition rather than concatenation and so the zeros will be lost. Using convert will avoid this problem.
Calling REPLICATE('0',6) to avoid typing '000000' is just wasteful ;-)
3 
SELECT replicate('0', 6 - len(employeeID)) + convert(varchar, employeeID) as employeeID FROM dbo.RequestItems WHERE ID=0
Improve this answer

Comments

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

1 Comment

Whoops. Actually, this gave me 7135.0 when I gave it 7135.
1

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')
Improve this answer

Comments

0

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`
Improve this answer

Comments

0

The simplest is always the best:

Select EmployeeID*1 as EmployeeID
Improve this answer

Comments

0

This will give the first six characters of EmployeeId, if the length of EmployeeId is greater than six. Also, this will left pad the EmployeeId with zeros if the length of EmployeeId is less than six.

Select REPLICATE('0',6-LEN(RTRIM(Left(EmployeeId, 6)))) + RTRIM(Left(EmployeeId, 6))
Improve this answer

Comments