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.

Since leading `0`s are significant to this field, why change it to an `INT` at all? — Oded, Mar 01 '12 at 17:09
SQL Server 2012 will **finally** have a `FORMAT` function like C# :-) — marc_s, Mar 01 '12 at 17:10
@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. — , Mar 01 '12 at 17:14
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? — HABO, Mar 01 '12 at 18:09
Possible duplicate of [Pad a string with leading zeros so it's 3 characters long in SQL Server 2008](https://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) — underscore_d, Mar 23 '18 at 14:26
@underscore_d - except the question you linked was asked on [May 26 '13 at 15:47]. Mine was asked about a year earlier on [Mar 1 '12 at 17:07]. Perhaps the other question should be marked as the duplicate. — , Mar 23 '18 at 18:16
I saw that, but if SO didn't want to allow people to mark duplicates back in time, they wouldn't allow it, so it doesn't bother me. Other than that, maybe; I guess in this case, I just found a handful and chose the higher-voted one as the target. — underscore_d, Mar 24 '18 at 15:08

Vince Pergolizzi · Accepted Answer · 2015-05-15T16:53:44.863

221

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)

edited May 15 '15 at 16:53

answered Mar 01 '12 at 17:10

Vince Pergolizzi

6,396
3
19
16

1

+1 You even showed me how to remove the 0s! Let me test this, and I'll mark it as an answer. – Mar 01 '12 at 17:15
1

you don't need code to remove the zeroes, just assign or convert to an int and they will be removed automatically. – Jimbo Mar 01 '12 at 17:25
Uh-oh! Came back from lunch to test. The `REPLICATE()` did not add any zeros to the number. I got back exactly what I fed into it. – Mar 01 '12 at 19:19
2

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. – Mordy Feb 13 '14 at 10:30
@Mordy that was my impression about EmployeeID, but the question states that the field is a char(6) so EmployeeID is a string – Steve Feb 13 '14 at 10:35
2

Though its old... using "+ convert (varchar, EmployeeID)" instead of "+ EmployeeID" should solve the int issue – Krishna Sarma Aug 28 '14 at 15:30
3

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) – Mahmoud Moravej Dec 23 '15 at 05:50
please add information about [`lpad`](https://www.w3schools.com/sql/func_mysql_lpad.asp) in yout answer – diralik Dec 23 '17 at 11:07
Why? This is a SQL Server question. – Vince Pergolizzi Mar 11 '18 at 02:56
In snowflake dwh you would go with concatenation: `SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) || RTRIM(EmployeeId)` – marcin2x4 Dec 11 '19 at 11:27

score 205 · Answer 2 · answered Jul 31 '14 at 19:41

205

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

answered Jul 31 '14 at 19:41

EdMorte

2,059
1
11
4

1

This is an old question - before SQL Server 2012 came out. – Aug 01 '14 at 16:22
30

Still, I'd like to see this bubble a little closer to the top now. – Dave Haynes Dec 16 '14 at 15:34
5

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')` – Ralph Oct 08 '15 at 15:51
5

This way is the simplest and IMHO the best solution. – Robert Lujo Jan 23 '16 at 02:06
Be aware of this function if you are using it for large dataset – amd Jun 29 '16 at 13:01
2

`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 https://stackoverflow.com/a/9520709/159145 instead. Here's an article from 2015 about `FORMAT`'s poor performance: https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but – Dai Oct 30 '19 at 06:19

Steve · Answer 3 · 2014-07-30T18:04:55.670

37

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.

edited Jul 30 '14 at 18:04

answered Mar 01 '12 at 17:12

Steve

213,761
22
232
286

1

This is the cleanest way of doing it I think. Thanks – iheartcsharp Mar 13 '13 at 16:07
Is that `'000000'` really necessary..? `'0'` also working fine. Is it safe to use just one 0 ..? – shashwat Oct 04 '13 at 16:37
1

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 – Steve Oct 04 '13 at 18:14
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 – nosklo Jan 29 '14 at 14:05

score 28 · Answer 4 · answered Jul 28 '14 at 21:45

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.

score 15 · Answer 5 · answered Oct 01 '15 at 07:16

15

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

answered Oct 01 '15 at 07:16

Raj kumar

1,275
15
20

This covers all needed options. Thank you. – kyurthich Aug 15 '17 at 15:51

score 9 · Answer 6 · answered Apr 22 '15 at 08:21

9

From version 2012 and on you can use

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

answered Apr 22 '15 at 08:21

Hagop Simonian

99
1
1

score 6 · Answer 7 · answered Dec 07 '21 at 21:24

6

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

FORMAT(EmployeeID, '000000;-00000')

answered Dec 07 '21 at 21:24

Danny C

111
2
1

score 6 · Answer 8 · edited Aug 21 '13 at 12:10

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

edited Aug 21 '13 at 12:10

SteveC

15,808
23
102
173

answered Mar 01 '12 at 17:19

Jamiec

133,658
13
134
193

@jp2code - you need to [read up on what StackOverflow is](http://stackoverflow.com/help/editing) - 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. – Jamiec Nov 26 '13 at 14:19

score 4 · Answer 9 · answered Jul 24 '13 at 12:08

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

answered Jul 24 '13 at 12:08

Divanshu

1,849
1
15
16

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. – Feb 12 '14 at 23:04
Calling REPLICATE('0',6) to avoid typing '000000' is just wasteful ;-) – Mladen Mihajlovic Aug 11 '16 at 19:07

score 3 · Answer 10 · edited Mar 01 '12 at 17:18

3

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

edited Mar 01 '12 at 17:18

Oleks

31,955
11
77
132

answered Mar 01 '12 at 17:15

Jimbo

2,529
19
22

score 2 · Answer 11 · answered Mar 01 '12 at 17:16

2

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

answered Mar 01 '12 at 17:16

user1227804

390
1
5

Whoops. Actually, this gave me `7135.0` when I gave it `7135`. – Mar 01 '12 at 19:26

score 1 · Answer 12 · answered Sep 05 '17 at 22:19

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

score 1 · Answer 13 · answered Sep 10 '19 at 14:34

1

The simplest is always the best:

Select EmployeeID*1 as EmployeeID

answered Sep 10 '19 at 14:34

Michal

11
1

Zlato010 · Answer 14 · 2018-06-05T08:17:28.180

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`

edited Jun 05 '18 at 08:17

answered Jun 05 '18 at 07:56

Zlato010

11
2

Formatting Numbers by padding with leading zeros in SQL Server

14 Answers14

Linked

Related