# 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

Thank you

Sunday, December 2, 2012 2:34 PM

• 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

• Proposed as answer by Sunday, December 2, 2012 6:14 PM
• Marked as answer by Sunday, December 2, 2012 6:18 PM
Sunday, December 2, 2012 6:08 PM

### All replies

• The following article is padding:

Sunday, December 2, 2012 3:21 PM
• 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

Sunday, December 2, 2012 3:42 PM
• 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

• Proposed as answer by Sunday, December 2, 2012 6:14 PM
• Marked as answer by Sunday, December 2, 2012 6:18 PM
Sunday, December 2, 2012 6:08 PM
```UPDATE TableName