Pad a string with leading zeros so it's 3 char

Question

I have a string that is up to 3 characters long when it's first created in SQL Server 2008 R2.

I would like to pad it with leading zeros, so if its original value was '1' then the new value would be '001'. Or if its original value was '23' the new value is '023'. Or if its original value is '124' then new value is the same as original value.

I am using SQL Server 2008 R2. How would I do this using T-SQL?

Answer 1

I have always found the following method to be very helpful.

REPLICATE('0', 5 - LEN(Job.Number)) + CAST(Job.Number AS varchar) as 'NumberFull'

Answer 2

If the field is already a string, this will work

 SELECT RIGHT('000'+ISNULL(field,''),3)

If you want nulls to show as '000'

It might be an integer -- then you would want

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

As required by the question this answer only works if the length <= 3, if you want something larger you need to change the string constant and the two integer constants to the width needed. eg '0000' and VARCHAR(4)),4

Answer 3

For those wanting to update their existing data here is the query:

update SomeEventTable set eventTime=RIGHT('00000'+ISNULL(eventTime, ''),5)

Answer 4

Simple is that

Like:

DECLARE @DUENO BIGINT
SET @DUENO=5

SELECT 'ND'+STUFF('000000',6-LEN(RTRIM(@DueNo))+1,LEN(RTRIM(@DueNo)),RTRIM(@DueNo)) DUENO

Answer 5

I know its old ticket I just thought to share it.

I found this code looking for a solution. Not sure if it works on all versions of MSSQL I have MSSQL 2016.

declare @value as nvarchar(50) = 23
select REPLACE(STR(CAST(@value AS INT) + 1,4), SPACE(1), '0') as Leadingzero

returns "0023" The 4 in the STR function is the total length including the value. Example 4, 23 and 123 will all have 4 in STR and the correct amount of zeros will be added. You can increase or decrease it. No need to get the length on the 23.

Edit: I see its the same as @Anon post.

Answer 6

For a more dynamic approach try this.

declare @val varchar(5)
declare @maxSpaces int
set @maxSpaces = 3
set @val = '3'
select concat(REPLICATE('0',@maxSpaces-len(@val)),@val)