'SQL - Select records with duplication in the same column

Is there a way to select records with duplication within 1 column?

So, for instance you have Address_Table:

Address_Line_1 Address_Line_2
123 street Town
321 street 321 street 321 street Town
456 street Town
789 street 789 street Town

Is there a way to select the all records like 321 & 789 street from the Address_Line_1 column that contain duplicates of themselves?

Thanks

sqlsql-servertsqlssms


Solution 1:[1]

Just a thought, and not fully tested.

Select A.* 
 From  YourTable A
 Cross Apply (
                select Dupes = Avg(Hits)   -- perhaps Max(Hits) instead
                 From (
                        Select Value
                              ,Hits   = sum(1) over (partition by Value)
                         From  string_split([Address_Line_1],' ')
                       ) B1
             ) B
 Where Dupes>1

Results

Address_Line_1                      Address_Line_2
321 street 321 street 321 street    Town
789 street 789 street               Town

Solution 2:[2]

If your databse COMPATIBILITY_LEVEL more than 130 
ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 130

You can try this

SELECT ads = STUFF((
     SELECT ' ' + value
        FROM STRING_SPLIT(Address_Line_1, ' ')
        GROUP BY value
        FOR XML PATH('')
     ), 1, 1, '') , Address_Line_1, Address_Line_2 FROM Adress

Fidler Sample

Sample Image

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 John Cappelletti
Solution 2 Ömer Faruk Ocako?lu

Related Questions