Reputation: 387
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?
Upvotes: 1
Views: 8699
Reputation: 31
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
Upvotes: 3
Reputation: 82020
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
Upvotes: 1
Reputation: 522817
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;
Upvotes: 4