CODEWITHSUNDEEP
sqlsql-server
RedRocket
RedRocket

Reputation: 1733

How to add leading zero when number is less than 10?

I have a column in my sql table. I am wondering how can I add leading zero to my column when my column's value is less than 10? So for example:

number   result
1     ->    01
2     ->    02
3     ->    03
4     ->    04
10    ->    10

Upvotes: 16

Views: 136034

Answers (8)

Shazeel Khalil
Shazeel Khalil

Reputation: 1

Zeros can be added on left side using function LPAD

SELECT LPAD('123', 5, "0");

the result is the following

00123

Syntax

LPAD(string, length, lpad_string)

SELECT LPAD(CustomerName, 30, "ABC") AS LeftPadCustomerName

FROM Customers;

Result

LeftPadCustomerName

ABCABCABCABAlfreds

Upvotes: 0

Bhavya Kesaria
Bhavya Kesaria

Reputation: 25

Formatting numbers to add leading zeros can be done in SQL Server. It is just simple. Lets create a new table and see how it works:

CREATE TABLE Numbers(Num INT);

Lets insert few values and see

INSERT Numbers VALUES('12');
INSERT Numbers VALUES('112');
INSERT Numbers VALUES('12');
INSERT Numbers VALUES('122');
INSERT Numbers VALUES('122');

Now we can see how the numbers are formatted with 6 digits, if it has less than 6 digits it will add leading zeros.

SELECT * FROM Numbers;

Num
12
112
12
122
122

Formatting:

SELECT RIGHT('00000'+ CONVERT(VARCHAR,Num),6) AS NUM FROM Numbers;

NUM
000012
000112
000012
000122
000122

OR

SELECT RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, Num), 6) AS NUM FROM Numbers;

NUM
000012
000112
000012
000122
000122

Upvotes: 0

Ali Habibi
Ali Habibi

Reputation: 21

In another method, you can Replace 10 to 2.

declare @ID int =32123;

substring('000000000',1,10-len(cast(@ID as varchar(10)))) +   cast(@Id as varchar(10))

Result: 0000032123

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use RIGHT:

SELECT RIGHT('0' + CAST(Number AS VARCHAR(2)), 2) FROM tbl

For Numbers with length > 2, you use a CASE expression:

SELECT
    CASE
        WHEN Number BETWEEN 0 AND 99
            THEN RIGHT('0' + CAST(Number AS VARCHAR(2)), 2)
        ELSE
            CAST(Number AS VARCHAR(10))
    END
 FROM tbl

Upvotes: 13

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

And yet one more solution:

SELECT REPLACE(STR(@YourNumber,2),' ','0');

I prefer this, as other approaches might lead to random results in case of numbers which are wider than the count of digits you've specified:

But it will not deal with negativ numbers...

DECLARE @YourNumber INT=123;
SELECT REPLACE(STR(@YourNumber,5),' ', '0')   --pad to five digits
      ,REPLACE(STR(@YourNumber,3),' ', '0')   --pad to 3 digits
      ,REPLACE(STR(@YourNumber,2),' ', '0');  --123 is wider than 2 digits...

The result

00123   
123 
**

Upvotes: 3

KumarHarsh
KumarHarsh

Reputation: 5094

another method,

select case when number <10 then replicate('0',1)+cast(number as varchar) 
else cast(number as varchar) end

Upvotes: 1

APH
APH

Reputation: 4154

Felix's solution is great, unless you have numbers above 100 in your table, in which case, try using a case statement instead:

Select case when Number between 1 and 9 
    then '0' else '' end + cast(number as varchar(3))

Upvotes: 2

CrimsonKing
CrimsonKing

Reputation: 2896

format(number,'00')

Version >= 2012

Upvotes: 37

Related Questions