SQL - Select records with duplication in the same column-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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