Oracle Intellect: August 2009

August 18, 2009

Find and Delete Duplicate Rows in a Table

To find duplicate rows:

SELECT column1, column2
  FROM table_name
 GROUP BY column1, column2
HAVING COUNT(*) > 1;

To delete duplicate rows:

DELETE FROM table_name a
 WHERE ROWID > (SELECT MIN(ROWID)
                  FROM table_name b
                 WHERE a.column1 = b.column1
                   AND a.column2 = b.column2
               );

String Tokenizer

Query to tokenize comma seperated string:


SELECT TRIM(SUBSTR( txt
                   ,INSTR(txt, ',', 1, level ) + 1
                   ,INSTR(txt, ',', 1, level+1) - INSTR(txt, ',', 1, level) - 1
                  )
           ) AS token
  FROM (SELECT ',' 'Comma,Seperated,String' ',' AS txt FROM DUAL )
CONNECT BY level <= LENGTH(txt) - LENGTH(REPLACE(txt,',','')) - 1;

Output:

TOKEN
------
Comma
Seperated
String