Adding / preserving leading zero while UPDATING a column

Question

I am generating some pin numbers in increamental order using the script below and would like to include a leading zero..tried all sort of data type and seem to be losing the leading zero. Would appreciate some help here in UPDATING my table

WITH PIN_NO
AS
(
    SELECT 4310 + ROW_NUMBER() OVER(ORDER BY Firstname, Lastname ) NEW_PIN, *
    FROM Citizen
)

UPDATE PIN_NO
SET  PIN = CAST(NEW_PIN AS VARCHAR(10))

Table name: Citizen

Firstname       Lastname    PINOO      Address  Age    other columns....
John             Smith                20001     19
Andrew           Evans                363252    30
Bill             Towny                63322     67
Dame             Beaut                34222     34

Would like it to be

Firstname       Lastname    PINOO     Address  Age    other columns....
John             Smith      04310    20001      19
Andrew           Evans      04311    363252     30
Bill             Towny      04312    63322      67
Dame             Beaut      04313    34222      34

Any other relevant script will help. Thanks

the keyword is "padding", have a look at http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008 for an answer — swe, Sep 14 '16 at 12:21

score 4 · Accepted Answer · answered Sep 14 '16 at 12:20

4

Prepend the number with enough zeroes and use RIGHT to truncate it to the required length.

WITH PIN_NO
AS
(
    SELECT 4310 + ROW_NUMBER() OVER(ORDER BY Firstname, Lastname ) NEW_PIN, *
    FROM Citizen
)
UPDATE PIN_NO
SET  PIN = RIGHT('0000000000' + CAST(NEW_PIN AS VARCHAR(10)), 10)

answered Sep 14 '16 at 12:20

Vladimir Baranov

31,799
5
53
90

works like charm...thanks V! The '10' at the end of the script, what is the purpose of that? – optimisteve Sep 14 '16 at 12:22
1

@optimisteve, The last `10`, the second parameter of the `RIGHT` function is the final length of the string that you want to get. For example, if you want your PIN to be 15 characters long, make the `'000...000'` string 15 chars long, and change both `10` to `15`. – Vladimir Baranov Sep 14 '16 at 23:09

Sandip - Frontend Developer · Answer 2 · 2016-09-14T12:36:00.030

1

Use Replicate / RIGHT Function:

Below example may help you:

DECLARE @tbl1 AS TABLE
(
    ITEM_NBR  VARCHAR(5),
    GRP_NBR VARCHAR(5)
)

INSERT INTO @tbl1 VALUES('0001',221)
INSERT INTO @tbl1 VALUES('0002',221)
INSERT INTO @tbl1 VALUES('0003',221)

REPLICATE:

SELECT 
    REPLICATE
    (
        '0',
        4-LEN(MAX(CAST(ITEM_NBR     as INT)))
    ) + 
    CAST( MAX(CAST(ITEM_NBR     as INT))+1 AS VARCHAR(5)) 
FROM @tbl1
GROUP BY GRP_NBR

RIGHT:

SELECT 
    RIGHT
    (
        (
            '00000000000'
            + 
            CAST( MAX(CAST(ITEM_NBR as INT))+1 AS VARCHAR(5))
        ) ,
        4
    ) 
FROM @tbl1
GROUP BY GRP_NBR

edited Sep 14 '16 at 12:36

answered Sep 14 '16 at 12:24

Sandip - Frontend Developer

14,397
4
35
61

Hi and thanks Sandip. Whats the use / need of the DECLARE in the script. – optimisteve Sep 14 '16 at 12:28
I have define one temp table with Declare and give you example with data – Sandip - Frontend Developer Sep 14 '16 at 12:29
thanks all. I thought the use of VARCHAR data type would normally preserve the leading zero – optimisteve Sep 14 '16 at 13:04
@Sandip...your script will only select the data. How about updating it? – optimisteve Sep 14 '16 at 13:06

Adding / preserving leading zero while UPDATING a column

2 Answers2

Linked