Delete rows without leading zeros

Question

I have a table with a column (registration_no varchar(9)). Here is a sample:

id  registration no
1   42400065
2   483877668
3   019000702
4   837478848
5   464657588
6   19000702
7   042400065

Please take note of registration numbers like (042400065) and (42400065), they are almost the same, the difference is just the leading zero.

I want to select all registration numbers that have the same case as above and delete the ones without a leading zero i.e (42400065)

pls, also note that before i delete the ones without leading zeros (42400065), i need to be sure that there is an equivalent with leading zeros(042400065)

Answer 1

declare @T table
(
  id int,
  [registration no] varchar(9)
)

insert into @T values
(1,   '42400065'),
(2,   '483877668'),
(3,   '019000702'),
(4,   '837478848'),
(5,   '464657588'),
(6,   '19000702'),
(7,   '042400065')

;with C as
(
  select row_number() over(partition by cast([registration no] as int) 
                           order by [registration no]) as rn
  from @T
)
delete from C
where rn > 1