0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Francesco Tattoli
  • 82
  • 1
  • 10

3 Answers3

1

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.*

Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • As I said before, before deleting I need to check the duplicates if they contain quantities, or prices, and only then (in the meanwhile I switch quantities from one article to another) I can proceed with deletion – Francesco Tattoli Jul 30 '15 at 09:21
  • @FrancescoTattoli: Updated my answer. – Raging Bull Jul 30 '15 at 09:27
0
delete from my_table
where my_column like '%,%';
Steven Eccles
  • 229
  • 1
  • 6
  • this will delete all the data containing "," anywhere in the column value.Please see what the user has asked for... – Kryptonian Jul 30 '15 at 09:07
  • Uhm, actually I first need to show all the records with similar codes, so I can check if I put quantities in the wrong record, and only when I double checked the results I can then proceed by deleting records – Francesco Tattoli Jul 30 '15 at 09:12
  • Explain how does your code-snippet work, where was the problem and how your code do solve it. – VP. Jul 30 '15 at 11:47
0

First hunt those records where there are duplicate values and then delete them.

select B.*
--delete B 
from
(select [column], replace(replace([column], ',', ''), '.', '') from [yourtable]
group by [column] having count(1) > 1)A
JOIN [yourtable] B
ON A.[column] = B.[column] and A.[column] like '%,%'

replace(replace([column], ',', ''), '.', '') will fetch the records sans , and . while count(1) > 1 will get records which are duplicates.

SouravA
  • 5,147
  • 2
  • 24
  • 49