删除不带前导零的行

发布于 2024-12-05 11:49:06 字数 367 浏览 8 评论 0原文

我有一个带有列的表（registration_no varchar(9)）。这是一个示例：

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

请注意注册号，例如（042400065）和（42400065），它们几乎相同，区别只是前导零。

我想选择与上面大小写相同的所有注册号，并删除没有前导零的注册号，即（42400065）

，另请注意，在删除没有前导零的注册号（42400065）之前，我需要确保有等价于前导零 (042400065)

原文

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)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦冥 2024-12-12 11:49:06

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

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

回复收藏 0 原文

时光是把杀猪刀 2024-12-12 11:49:06

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;

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;

回复收藏 0 原文

御守 2024-12-12 11:49:06

我认为您可以使用单个 DELETE 语句来完成此操作。 JOIN 确保只有重复项才能被删除，并且约束通过不以“0”开头的注册号进一步限制它。

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

运行上述 DELETE 后，您的表看起来像这样。我在 SQL Server 2008 实例上测试了它。

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

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

回复收藏 0 原文

雨落星ぅ辰 2024-12-12 11:49:06

该解决方案不依赖于特定长度的注册号，它只是查找具有相同整数但不相同值（因为前导零）的注册号，并选择具有“0”的条目作为第一个字符。

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

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

回复收藏 0 原文

~没有更多了~