optimisteve optimisteve - 2 months ago 7
SQL Question

Adding / preserving leading zero while UPDATING a column

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

Answer

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)