Solved

How to remove duplicate records in SQL2008r2 where there is a column with identical values except one is UPPER case and the other is LOWER case ?

Posted on 2015-01-13
8
84 Views
Last Modified: 2015-01-14
I have inherited tables with duplicate records and I'm able to remove most except in one table I have a column that have records with identical values except one is UPPER case and the other is LOWER case.  I verify with the client that the two identical values are correct, one UPPER and the other LOWER.  How do I remove the duplicates?

I have included code to create a test table, load the table and the query that will search for the duplicates.
GRP-ID.sql
0
Comment
Question by:wdbates
  • 4
  • 2
  • 2
8 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40547649
Instead of a SELECT, make the final statement a DELETE:

DELETE
  FROM CTE
WHERE DuplicateCount > 1
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40547654
You can use case insensitivity using the COLLATE function - see http://msdn.microsoft.com/en-us/library/ms184391.aspx for more details.
0
 

Author Comment

by:wdbates
ID: 40547671
Scott, you missed the point, yes the query will be my delete query, but create and load the table and execute the CTE starting at line 57 through 81 and see the results.

Hello Phillip, glad to hear from you again.  I will give your suggestion alook and get back to the group.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40547728
Ok; good luck.
0
 

Author Comment

by:wdbates
ID: 40547736
Hello Phillip, I must be missing something and below is what I have tried;

SELECT SERVERPROPERTY ('Collation')  --To determine collation
GO

SQL_Latin1_General_CP1_CI_AS  

--Test Query

SELECT *
       FROM [dbo].[UPPERlower]
       WHERE [GRP_ID] LIKE '%954.Atx%' Collate SQL_Latin1_General_CP1_CI_AS

GRP_ID      GRP_DESC      ORD_ID      INTERFACE_DATE
954.Atx      NULL             OUTSIDE      NULL
954.Atx      NULL             OUTSIDE      NULL
954.Atx      NULL             OUTSIDE      NULL
954.ATX      NULL            OUTSIDE      NULL
954.ATX      NULL            OUTSIDE      NULL
954.ATX      NULL            OUTSIDE      NULL

I thought that the returning results would only be the records where GRP_ID = '954.Atx'
0
 

Author Comment

by:wdbates
ID: 40547748
I may have it.  Going to a meeting will try later tonight or early tomorrow.

  SELECT *
       FROM [dbo].[UPPERlower]
       WHERE [GRP_ID] Collate Latin1_General_CS_AS = '954.Atx'

GRP_ID      GRP_DESC      ORD_ID      INTERFACE_DATE
954.Atx      NULL      OUTSIDE      NULL
954.Atx      NULL      OUTSIDE      NULL
954.Atx      NULL      OUTSIDE      NULL
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40548450
What about

SELECT DISTINCT *
       FROM [dbo].[UPPERlower]
       WHERE [GRP_ID] LIKE '%954.Atx%' Collate SQL_Latin1_General_CP1_CI_AS

Open in new window


or

SELECT GRP_ID, GRP_DESC, ORD_ID, INTERFACE_DATE
       FROM [dbo].[UPPERlower]
       GROUP BY [GRP_ID] Collate SQL_Latin1_General_CP1_CI_AS,
       , GRP_DESC, ORD_ID, INTERFACE_DATE

Open in new window

0
 

Author Closing Comment

by:wdbates
ID: 40549024
Hello Phillip;

That was the fix.  Code and results below.

Thank you very much.

WITH CTE
      (
       GRP_ID
      ,GRP_DESC
      ,ORD_ID
      ,DuplicateCount
      )
AS
   (
      SELECT CASE SUBSTRING([GRP_ID], 1, 6)
                    WHEN 't' THEN '954-Atx' Collate Latin1_General_CS_AS
                    ELSE GRP_ID
               END
            ,GRP_DESC
            ,ORD_ID
              ,ROW_NUMBER()
                  OVER(PARTITION BY CASE SUBSTRING([GRP_ID], 1, 6)
                                                  WHEN 't' THEN '954-Atx' Collate Latin1_General_CS_AS
                                                  ELSE GRP_ID
                                            END+ISNULL(GRP_DESC, '')+ISNULL(ORD_ID, '')
                          ORDER BY CASE SUBSTRING([GRP_ID], 1, 6)
                                                  WHEN 't' THEN '954-Atx' Collate Latin1_General_CS_AS
                                                  ELSE GRP_ID
                                            END+ISNULL(GRP_DESC, '')+ISNULL(ORD_ID, '')) AS DuplicateCount
       FROM [dbo].[UPPERlower] --ORDER BY GRP_ID Collate Latin1_General_CS_AS, DuplicateCount DESC
   )
SELECT *
  FROM CTE
 WHERE DuplicateCount > 1
  ORDER BY GRP_ID
GO

GRP_ID      GRP_DESC      ORD_ID      DuplicateCount
954.Atx      NULL      OUTSIDE      2
954.Atx      NULL      OUTSIDE      3
954.ATX      NULL      OUTSIDE      2
954.ATX      NULL      OUTSIDE      3
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question