You’re offline. This is a read only version of the page.
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
I have the same question (0)
Here you go:
Share
Report
All responses (
Answers (
26,225
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;
