How to add leading zeros in SQL Server

Question

I have a question about SQL Server: how to add leading three 000 (zeros) while id does not have leading zeros in SQL Server?

CREATE TABLE [dbo].[ids]
(
     [id] [VARCHAR](50) NULL,
     [name] [VARCHAR](50) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[ids] ([id], [name]) VALUES (N'09', N'abc')
GO

INSERT INTO [dbo].[ids] ([id], [name]) 
VALUES (N'0098', N'de'), (N'987', N'j'), (N'00056', N'i'),
       (N'6', N'z'), (N'0908', N'u'),
       (N'99999999', N'u'), (N'7522323838483', N'i')
GO

Based on above data I want output like below :

name    | id
--------+-----------
abc     | 0009
de      | 00098
j       | 000987
i       | 00056
z       | 0006
u       | 000908
u       | 00099999999
i       | 0007522323838483

I tried like this:

SELECT 
    RIGHT('000' + id, 3) id, [name]
FROM 
    [dbo].[ids]

but above query is not returning the expected result.

Can you please tell me how to write a query to achieve this task in SQL Server?

Answer 1

You can use format(), with '0' as a custom specifier to do this very simply.

select NOPER, FORMAT( NOPER, '000000') as NOPER_formatted
from mytable

NOPER       NOPER_formatted
----------- ------------------------------------
100         000100
101         000101
102         000102
10334       010334
10335       010335
10336       010336

For more information see the link below : https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-numeric-format-strings

Answer 2

Just another thought is to use try_convert() if your ID strings are are numeric

Example

Select *
      ,NewValue = '000'+left(try_convert(bigint,id),25)
 From  ids

Returns

id              name    NewValue
09              abc     0009
0098            de      00098
987             j       000987
00056           i       00056
6               z       0006
0908            u       000908
99999999        u       00099999999
7522323838483   i       0007522323838483

Answer 3

You could try stripping leading zeroes, then concatenating three zeroes to the front, e.g.

SELECT
    id,
    '000' + SUBSTRING(id, PATINDEX('%[^0]%', id + '.'), LEN(id)) AS id_out,
    name
FROM ids;

Demo