Select all duplicate records

Question

How do I return all records that have the same last 6 digits or characters?
For example:
000123456
009123456
123456789
100456789
201456789
987654321
112233445

I need to return:
000123456
009123456
123456789
100456789
201456789

Answer 1

;With cte As 
(Select YourColumn, 
  Count(*) Over(Partition By Right(YourColumn, 6)) As cnt
From YourTable)
Select YourColumn
From cte
Where cnt > 1;

Replace YourColumn and YourTable with the your column name and table name.
Tom

Answer 2

Not working...
it only returns:
000123456
201456789

Answer 3

DECLARE @T TABLE (
    Col varchar(20)
);
INSERT INTO @T VALUES
('000123456'),
('009123456'),
('123456789'),
('100456789'),
('201456789'),
('987654321'),
('112233445');

SELECT t.Col
FROM @T AS t
INNER JOIN (
    SELECT RIGHT(Col, 6) AS [6_Digits]
    FROM @T
    GROUP BY RIGHT(Col, 6)
    HAVING COUNT(*) > 1
) AS g ON g.[6_Digits] = RIGHT(t.Col, 6);

Answer 4

Hi @Jen ,

Please find below a minimal reproducible example.
It is a copycat from the Tom Cooper solution.
All credit goes to Tom.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col char(9));  
INSERT INTO @tbl VALUES  
('000123456'),  
('009123456'),  
('123456789'),  
('100456789'),  
('201456789'),  
('987654321'),  
('112233445');  
-- DDL and sample data population, end  
  
;WITH rs AS   
(  
  SELECT id, col,   
    COUNT(*) OVER(PARTITION BY RIGHT(col, 6)) AS cnt  
  FROM @tbl  
 )  
 SELECT id, col, cnt  
 FROM rs  
 WHERE cnt > 1  
 ORDER BY Right(col, 6);  

Output

+----+-----------+-----+  
| id |    col    | cnt |  
+----+-----------+-----+  
|  1 | 000123456 |   2 |  
|  2 | 009123456 |   2 |  
|  3 | 123456789 |   3 |  
|  4 | 100456789 |   3 |  
|  5 | 201456789 |   3 |  
+----+-----------+-----+  

Answer 5

Hi YitzhakKhabinsky-0887,

it worked great, thanks
I have given credit to Tom Cooper per your comments, tried to give to both of you but it wouldn't le me.