How to pad a number with zero?

Question

A table was built, however, there is one column for the District School. This field should be in a 0000 format, however when the table was built, it dropped the leading zeros, so, if a District School number was 0001, it came in as 1, same as if the DS number was 0052, it came in as 52, but I need to alter that column so that all values are reflected as 0000. How do I go about doing that?

Numbers have no leading zeroes. 52 is the same as 0052. If you want the field to have leading zeroes it should be a `varchar`. Why store the 0s though and not just format the number when displaying it? — Panagiotis Kanavos, Oct 08 '20 at 18:14
See here: https://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008 and here: https://stackoverflow.com/questions/121864/most-efficient-t-sql-way-to-pad-a-varchar-on-the-left-to-a-certain-length — AbsoluteBeginner, Oct 08 '20 at 19:25
Does this answer your question? [Pad a string with leading zeros so it's 3 characters long in SQL Server 2008](https://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) — Dale K, Oct 08 '20 at 20:14

score 2 · Accepted Answer · edited Oct 08 '20 at 20:13

2

SELECT RIGHT('0000'+ISNULL(field,''),4)

edited Oct 08 '20 at 20:13

Dale K

25,246
15
42
71

answered Oct 08 '20 at 19:10

TK Bruin

472
4
15

score 0 · Answer 2 · edited Oct 08 '20 at 18:12

0

CREATE FUNCTION [dbo].[strCeros]
(
    @numero     INT,
    @cifras     TINYINT
)
RETURNS NVARCHAR 
(
    25
)
AS
BEGIN
    DECLARE @str NVARCHAR(25)
    IF @cifras > 25
        SET @cifras = 25
    
    SET @str = RIGHT(
            REPLICATE('0', @cifras) + CAST(@numero AS NVARCHAR(25)),
            @cifras
        )
    
    RETURN @str
END
GO

SELECT dbo.strCeros(99, 4) padZeros

edited Oct 08 '20 at 18:12

Alexander Volok

5,630
3
17
33

answered Oct 08 '20 at 18:04

Leonardo Martín Martínez Núñez

1
1
1

score 0 · Answer 3 · answered Oct 08 '20 at 18:15

select id , REPLICATE('0', 4 - len( cast(id as nvarchar) ) ) + cast(id as nvarchar)
from (
        select 1 id
        union
        select 5 id
        union
        select 8 id
        union
        select 19 id
        union
        select 33 id
        union
        select 52 id
    ) dts

How to pad a number with zero?

3 Answers3