Search code examples
sqlsql-server-2008-express

SQL Server 2008 Express : find similar records in a table

Here is my first question about a SQL Server 2008 Express database, containing articles, quantities, prices and so on. Unfortunately when I loaded data the first time I loaded articles making the following mistake:

I created two records for (almost) every article very similar but with a different character.

Here's an example:

  • TUTO510088.9X3
  • TUTO510088,9X3

The only different character is , and .. Is there a way to SELECT all these articles to DELETE the ones with the comma and leaving the others?

Please note that the position of the different character is not fixed, it could be on the 3rd from right, or in the middle

Solution

  • You can do it using a self join:

    delete T2
from TableName T1 JOIN
     TableName T2 on Replace(T1.article,'.','')=Replace(T2.article,',','')
where T2.article like '%,%'

    Demo in SQL Fiddle

    You can check which records are going to be deleted by replacing delete T2 by select T2.*