Add leading zeros

Transact-SQL
1

I have integers as data type varchar in a SQL Server 2012 table. I need to add leading zeros so all numbers in the column are 12 characters.

serial_number_column
1
2
3

Needs to be:
000000000001
000000000002
000000000003

Any help would be greatly appreciated.

Thanks,

sfjtraps

2

You can use the RIGHT function for this. Here's an example using a string:

declare @s varchar(20)

set @s = '111'

select right('000000000000'+@s, 12)

To update the data (TEST it first):

update t1
set @c1 = right('000000000000'+c1, 12)

2 Likes
3

It took me a minute to figure it out for my application, but it worked great. Thank you!!

4

You're welcome, glad to help.