SQL compare values rows in same table

Question

I have a database, I need to search double login.

Type : Char

Example :

15-Abc 
15-Deg
17-Abc

I need to find users like 15/17-Abc, double login,but different first digits How can I search does logins? (I have Thousands of them)

Is the format always two numbers, a dash, then three letters? — Keith, Aug 22 '17 at 11:25
Possible duplicate of [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Ocaso Protal, Aug 22 '17 at 11:29

score 0 · Answer 1 · answered Aug 22 '17 at 11:27

Hmmm . . . you can use exists to find matches on the second part. One method would be to split the string. Another is to just use string manipulation functions:

select t.*
from t
where login like '%-%' and
      exists (select 1
              from t t2
              where t2.login <> t.login and
                    t2.login like '%-%' and
                    substring(t2.login, charindex('-', t2.login), len(t2.login)) = substring(t.login, charindex('-', t.login), len(t.login))
             );

score 0 · Answer 2 · answered Aug 22 '17 at 11:27

This way you find all the duplicate parts:

declare @t table (col varchar(100));
insert into @t values
('15-Abc'),
('15-Deg'),
('17-Abc');

select substring(col, 3, len(col))
from @t
group by substring(col, 3, len(col))
having count(*) > 1;

If you want the rows of original table use this code:

declare @t table (col varchar(100));
insert into @t values
('15-Abc'),
('15-Deg'),
('17-Abc');

with cte as
(
select substring(col, 3, len(col)) as same_part
from @t
group by substring(col, 3, len(col))
having count(*) > 1
)

select *
from @t t
where  exists(select * from cte c where substring(t.col, 3, len(col)) = c.same_part);

SQL compare values rows in same table

2 Answers2