Dear,
I have a column on an sql query and I want to add either one or two zeros before the numbers in my SQL query result depending on length of the value.
Column results have a max of 7 digit numbers in it but at times it will only have 5, 6 or 7.
So when a number result is 12345 I want it to show up as 0012345, or when it's 123456 I want it to show as 0123456, or if it's 1234567 then nothing changes.How can we achieve this in Microsoft Sql Server.
RomRyan
*This post is locked for comments
Here you go:
Share
Report
All responses (
Answers (
declare @t table (SomeNumber int);
insert into @t values(12345);
insert into @t values(123456);
insert into @t values(1234567);
select SUBSTRING('0000000', 1, 7 - LEN(CONVERT(varchar(20), SomeNumber))) + CONVERT(varchar(20), SomeNumber) from @t;
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156