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?

@gbalu take some time and read this: meta.stackoverflow.com/questions/251078/… — forpas
– forpas, Commented Aug 18, 2019 at 15:10

Tim Biegeleisen · Accepted Answer · 2019-08-18 14:34:27Z

4

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

answered Aug 18, 2019 at 14:34

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zohar Peled Over a year ago

Nice, though this can be simplified using stuff, like this: STUFF(Id, 1, PATINDEX('%[^0]%', id) - 1, '000') AS id_out

Tim Biegeleisen Over a year ago

@ZoharPeled Good STUFF, no pun intended :-P

Tsubaki · Accepted Answer · 2021-08-09 13:21:01Z

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

John Cappelletti · Accepted Answer · 2019-08-18 14:44:52Z

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

But why? That only introduces the possibility (probability?) of a logical (though not an actual) conversion error.
@SMor A) It is just another approach. B) I did have the caveat if data was numeric C) Don't think it warrants a down vote.

Collectives™ on Stack Overflow

How to add leading zeros in SQL Server

3 Answers 3

Demo

2 Comments

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Linked

Related