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
87 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 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