sql find and remove duplicate columns with trim

Question

I have a table "partner_entries" with 3 columns : our_ref | partner_ref | partner_tag where all thoses 3 fields form the primary key.

I have a certain amount of "duplicate" rows like this :

42 | abc | tag1  
42 | abc  | tag1 

As you can see it's not really duplicate because there is a space after the same word. I can retrieve duplicates based on our_ref and partner_tag like this :

SELECT   COUNT(*) AS nb_duplicates, our_ref, partner_tag
FROM     partners_entries
GROUP BY our_ref, partner_tag
HAVING   COUNT(*) > 1

But it also take some rows where partner_ref is really different, i just want to select thoses where partner_ref is the same but with a space after, how can I do that ?

Thanks for your help

Answer 1

Use TRIM in the select and GROUP BY clauses:

SELECT   COUNT(*) AS nb_duplicates, our_ref, TRIM(BOTH '\n' FROM partner_ref), partner_tag
FROM     partners_entries
GROUP BY our_ref, TRIM(BOTH '\n' FROM partner_ref), partner_tag
HAVING   COUNT(*) > 1

Use an extended TRIM syntax to remove the newlines and other symbols from your data

SQL Fiddle