Append 00 depending on Length

Question

Hi

I have a Coulmn  which have Length 9 ,    Data in that Coulmn Varies with different Length.  I need to modify the length to 10 by padding 0 infront

 NationalIDNumber APPEND_ ZERO
1441                0000001441
690627818 0690627818
844973625        0844973625
23306                0000023306
565090917        0565090917
494170342        0494170342
9659517                009659517
443968955        0443968955

Please help me

Thank you

Answer 1

The following article is padding:

http://www.sqlusa.com/bestpractices/pad/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

Try

select NationalIDNumber, substring('0000000000',1,10-len(ltrim(rtrim(NationalIDNumber))))+ltrim(rtrim(NationalIDNumber)) APPEND_ ZERO

from your_tab;

go

---

Many Thanks & Best Regards, Hua Min

Answer 3

Here are some options for your scenario:

DECLARE @t TABLE ([NationalIDNumber] INT)
INSERT INTO @t VALUES(1441)           
INSERT INTO @t VALUES(690627818)
INSERT INTO @t VALUES(844973625)
INSERT INTO @t VALUES(23306) 
INSERT INTO @t VALUES(565090917)
INSERT INTO @t VALUES(494170342)
INSERT INTO @t VALUES(9659517)    
INSERT INTO @t VALUES(443968955)
INSERT INTO @t VALUES(9)
INSERT INTO @t VALUES(0)
INSERT INTO @t VALUES(999999999)
INSERT INTO @t VALUES(NULL)

SELECT 
STUFF([NationalIDNumber],1,0,REPLICATE('0',10-LEN([NationalIDNumber]))),
REPLICATE('0',10-LEN([NationalIDNumber]))+CAST([NationalIDNumber] AS VARCHAR(9)),
RIGHT('0000000000'+CAST([NationalIDNumber] AS VARCHAR(9)),10),
RIGHT(LEFT([NationalIDNumber]/10000000000,12),10)
FROM @t
WHERE [NationalIDNumber] >= 0

---

Jon

Answer 4

How about:

UPDATE TableName
SET Append_Zero = RIGHT('0000000000'+ CONVERT(VARCHAR,NationalIDNumber),10)

---

-- Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.