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)

score 2 · Accepted Answer · answered Sep 17 '11 at 17:14

2

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

answered Sep 17 '11 at 17:14

Mikael Eriksson

136,425
22
210
281

+1. Was thinking about converting to `int` too, but wasn't yet sure how to use that by the time I saw your solution. And it seems most optimal to me, really nice! – Andriy M Sep 17 '11 at 23:04

score 0 · Answer 2 · answered Sep 17 '11 at 16:08

create table temp id int;
insert into temp select id from your_table a where left (registration_no, ) = '0' and 
   exists select id from your_table 
              where a.registration_no = concat ('0', registration_no)

delete from your_table where id in (select id from temp);

drop table temp;

Cᴏʀʏ · Answer 3 · 2011-09-17T16:22:41.410

I think you can do this with a single DELETE statement. The JOIN ensures that only duplicates can get deleted, and the constraint limits it further by the registration numbers that don't start with a '0'.

DELETE 
    r1
FROM
    Registration r1
JOIN
    Registration r2 ON RIGHT(r1.RegistrationNumber, 8) = r2.RegistrationNumber
WHERE
    LEFT(r1.RegistrationNumber, 1) <> '0'

Your table looks like this after running the above DELETE. I tested it on a SQL Server 2008 instance.

ID          RegistrationNumber
----------- ------------------
2           483877668
3           019000702
4           837478848
5           464657588
7           042400065

score 0 · Answer 4 · answered Sep 17 '11 at 16:44

This solution won't depend on the registration numbers being a particular length, it just looks for the ones that are the same integer, yet not the same value (because of the leading zeroes) and selects for the entry that has a '0' as the first character.

DELETE r
FROM Registration AS r
JOIN Registration AS r1 ON r.RegistrationNo = CAST(r1.RegistrationNo AS INT)
    AND r.RegistrationNo <> r1.RegistrationNo
WHERE CHARINDEX('0',r.registrationno) = 1

Delete rows without leading zeros

4 Answers4