SQL Query to find duplicates separated by a letter-CodePudding

I have an excel sheet of law school courses and I need to find the duplicates. How duplicates are described in this scenario is the course number with a letter at the end.

Ex course number 500 and the duplicates of that course would be 500A, 500B, 500c etc. I need to single out all the duplicates

CodePudding user response：

Suggestion of resolution: If you have the data INSERT INTO "main"."courses" ("id", "name") VALUES ('1', '500'),('2', '400'),('3', '500A'),('4', '500B'),('5', '300'); You can execute the following request:

with cte as (select id, name, case when length(name)> 3 then SUBSTR(name, 1,LENGTH(name)-1) else name end as root
from courses )
select *
from courses a
inner join cte b on b.root = a.name
where a.name != b.name

It's just an exemple. Depending on your database management system, there are different ways to truncate the last character of the course number.