Compare two columns within the same table

[Teki07]

Hi,
I have a query that finds any duplicates caused by spacing.
This is my query


Select a.column1, b column1
from table1 as a, table1 as b
where replace (a.column1,' ','') = replace (b.column1,' ','')
and a.column1 <> b.column1

this is my result

column1 column1_1
A+ A +
A + A+
B+ B +
B + B+
C+ C +
C + C+

Now I need to compare row 1 of column1 with row 2 of column1_1 to eliminate the duplication

Any idea or advice?

 

[SantaMufasa]

Teki,

I presume that you want, as your result set, all distinct values (without any intervening blanks). If that is the case, then this code should work for you:

select * from table1;

COLUM
-----
A+
A +
B+
B +
C+
C +

6 rows selected.

col x heading "Distinct|Values" format a8
select distinct replace(column1,' ','') x from table1
 order by 1
/

Distinct
Values
--------
A+
B+
C+

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Teki07]

SantaMufasa,
Thank you but I think I did not explain myself right.

I have a table with a row that must be unique. However, some of the records have spacing on it which does not make it unique. I created the first query to find out the duplicated records due to spacing.

My results from my first query are

rows column1 column1_1
--------------------------
1 A+B A +B
2 A +B A+B
3 C+D C +D
4 C +D C+D

Now I need to eliminate my duplication becuase as you can see row 1 column 1 is the same as row 2 column1_1 and row 3 column1 is the same as row 4 column1_1 and so forth

I tried this

Select a.column1, b column1
from table1 as a, table1 as b
where replace (a.column1,' ','') = replace (b.column1,' ','')
and a.column1! = b.column1
and instr(acolumn1, ' ')='0'

which helps a little bit but if both records contains spaces then it will not show on my results.

Do I make sense on what I want??

Thank you so much

 

[Teki07]

To explain a little bit better the results, I need to find the duplicate records due to spacing so it can be fix in the database so I want to see

record 1 which is AB has a duplication due to spacing, that record is A B. I need to see both so I am comparing the same column. Once I compare I need to get rid of the duplication which is where I am stock.

Select a.column1, b column1
from table1 as a, table1 as b
where replace (a.column1,' ','') = replace(b.column1,' ','')
and a.column1! = b.column1

Please help get rid of the duplication records from my results.

 

[Turkbear]

Hi,
maybe Try this to get the result set:

Select a.column1, b column1
  from table1 as a, table1 as b 
 where replace (a.column1,' ','') = replace(b.column1,' ','')
MINUS
Select a.column1, b column1
  from table1 as a, table1 as b 
  a.column1! = b.column1

That should leave you with rows where the spacing is the only thing that makes them = ( Maybe)

If that works, you can then add a delete from SQL style command:

DELETE FROM
(
Select * From
(Select a.column1, b column1
  from table1 as a, table1 as b 
 where replace (a.column1,' ','') = replace(b.column1,' ','')
MINUS
Select a.column1, b column1
  from table1 as a, table1 as b 
  a.column1! = b.column1)
)

Which should get rid of those records.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Teki07]

Turkbear,
Thank you for the suggestion but it did not work the sql did not execute.

 

[Turkbear]

Hi,
Which sql did not exedute - any error messages?

(The second sql - the DELETE FROM - may not be supported in 9, it is in 11, I know)



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Teki07]

no, the fist one
SELECT a.column1, b.column1
FROM table1 a, table1 b
WHERE REPLACE (a.column1, ' ', '') = REPLACE (b.column1, ' ', '')
MINUS
SELECT a.column1, b.column1
FROM table1 a, table1 b
WHERE a.column1 <> b.column1

 

[Turkbear]

Hi,
Any error message or just no rows returned.

Try each part on its own and see what rows are returned.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Tek,

I just ran the code and it returned these results:

SELECT a.column1, b.column1
  FROM table1 a, table1 b
 WHERE REPLACE (a.column1, ' ', '') = REPLACE (b.column1, ' ', '')
MINUS
SELECT a.column1, b.column1
  FROM table1 a, table1 b
 WHERE a.column1 <> b.column1;

COLUM COLUM
----- -----
A +   A +
A+    A+
B +   B +
B+    B+
C +   C +
C+    C+

6 rows selected.

Please post the error message that you receive.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Teki07]

They both work if I run them separate but once I joined them with the minus. i just tried to excute for a long time and it hangs

 

[Turkbear]

Hi,
Santa, The 3 rows that appear to be equal ( A+ A+,B+ B+,C+ C+) should have been eliminated by the <> in the MINUS query, so maybe:

SELECT a.column1, b.column1
  FROM table1 a, table1 b
 WHERE REPLACE (a.column1, ' ', '') = REPLACE ((b.column1, ' ', '')
MINUS
SELECT a.column1, b.column1
  FROM table1 a, table1 b
 WHERE TRIM(a.column1) <> TRIM(b.column1);

would work better, getting rid of any leading or trailing spaces.

Tek

How is that column defined? CHAR or VARCHAR2?










To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Turkbear,

Your above code (once we remove the extraneouse paren, <smile>) produces these results:

SELECT a.column1, b.column1
  FROM table1 a, table1 b
 WHERE REPLACE (a.column1, ' ', '') = REPLACE (b.column1, ' ', '')
MINUS
SELECT a.column1, b.column1
  FROM table1 a, table1 b
 WHERE TRIM(a.column1) <> TRIM(b.column1)
/

COLUM COLUM
----- -----
A +   A +
A+    A+
B +   B +
B+    B+
C +   C +
C+    C+

6 rows selected.

Is this what you expected?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
Sorry, Senior Moment - that code will not answer the problem ( maybe for several reasons) since the problem spans multiple records -
How about creating a new table like ( similar to Santa's first reply)

CREATE TABLE HOLD_VALUES AS SELECT TRIM(REPLACE (a.column1, ' ', '')),a.*
FROM CURENT_TABLE a;

which should format all rows with no spaces anywhere in column1. Then creating and applying a UNIQUE constraint ( like a UNIQUE INDEX on col1) should eliminate the dups.





To Paraphrase:"The Help you get is proportional to the Help you give.."