0

Someone here was kind enough to provide a script to help me generate numbers in increment so it could look like the PIN number column below. I ran the script and noticed the minimum PIN number generated is 10000 which really should be 4310
Table name: Citizen

Firstname       Lastname    PIN        Address  Age    other columns....
John             Smith      4310      20001     19
Andrew           Evans      4311      363252    30
Bill             Towny      4312      63322     67
Dame             Beaut      4313      34222     34



WITH PIN_NO
AS
(
SELECT 4310 + ROW_NUMBER() OVER(ORDER BY [All LP First Name]) NEW_PIN, *    
FROM [dbo].[C3MDMRevsBens]
)

UPDATE PIN_NO
SET   [Account Reference Number]  = CAST(NEW_PIN AS VARCHAR(10))

When I ordered the PIN number in asc order, below are the numbers generated starting from record 1 .....

  select * from Citizen order by PIN asc

OR 

 SELECT MIN(PIN) FROM Citizen

.

10000
100000
100001
100002
100003
100004
100005
100006
100007
100008
100009
10001

Thanks

optimisteve
  • 101
  • 1
  • 9
  • 1
    Based on your output data, it appears PIN is defined as a varchar or nvarchar column. You need to either change the column definition to a numeric (int, for example), or if that's not possible, you need to `CAST` the column in your `ORDER BY` clause. – msturek Sep 14 '16 at 15:19
  • based on question http://stackoverflow.com/questions/39490273/adding-preserving-leading-zero-while-updating-a-column – swe Sep 14 '16 at 15:27

1 Answers1

1

your query works fine... I've tested. As @bassrek says it's your select query that has to be reviewed:

select * from Citizen order by CAST(Pin AS int)

Because Pin seems to be a nvarchar and 10000 comes before 4.

So, you have to change pin column to int or use a cast in select.

ZeroWorks
  • 1,618
  • 1
  • 18
  • 22
  • PERFECT....all I had to do was change from nvarchar to int. Sounds like I need use 'int' henceforth wherever possible.... – optimisteve Sep 14 '16 at 15:47