how to find full duplicate strings? Not single values, strings. Oracle...

SELECT 1col, 2col, 3col, COUNT(*)
FROM tab
GROUP BY 1col, 2col, 3col
HAVING COUNT(*) > 1

But this solution is primitive and not elegant

CodePudding user response：

An alternative which might run faster would be to use string concatenation:

SELECT 1col || 2col || 3col AS string
FROM tab
GROUP BY 1col || 2col || 3col
HAVING COUNT(*) > 1;

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row pattern matching:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY col1, col2, col3  -- all the columns
  ORDER BY col3                  -- can be anything as PARTITION BY contains all the
                                 -- columns
  PATTERN (^ any_row{2} )        -- look for the first 2 rows in the partition
  DEFINE
    any_row AS 1 = 1             -- does not matter as the PARTITION BY clause is
                                 -- separating the rows into unique groups
)

Which, for the sample data:

CREATE TABLE table_name ( col1, col2, col3 ) AS
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 1, 1, 1 FROM DUAL UNION ALL
SELECT 1, 2, 3 FROM DUAL UNION ALL
SELECT 1, 2, 3 FROM DUAL UNION ALL
SELECT 2, 3, 4 FROM DUAL;

Outputs:

fiddle

CodePudding user response：

You can find duplicate rows using analytic function or an inline view.

CREATE TABLE fruits (
        fruit_id   NUMBER generated BY DEFAULT AS IDENTITY,
        fruit_name VARCHAR2(100),
        color VARCHAR2(20)
);

INSERT INTO fruits(fruit_name,color) VALUES('Apple','Red');
INSERT INTO fruits(fruit_name,color) VALUES('Apple','Red');
INSERT INTO fruits(fruit_name,color) VALUES('Orange','Orange');
INSERT INTO fruits(fruit_name,color) VALUES('Orange','Orange');
INSERT INTO fruits(fruit_name,color) VALUES('Orange','Orange');
INSERT INTO fruits(fruit_name,color) VALUES('Banana','Yellow');
INSERT INTO fruits(fruit_name,color) VALUES('Banana','Green');

/* analytic function */

WITH fruit_counts AS (
    SELECT f.*,
        COUNT(*) OVER (PARTITION BY fruit_name, color) c
    FROM fruits f
)
SELECT *
FROM fruit_counts
WHERE c > 1 ;

/* inline view */

SELECT 
    *
FROM
        (SELECT f.*,
            COUNT(*) OVER (PARTITION BY fruit_name, color) c
        FROM fruits f
        )
WHERE c > 1;

FRUIT_ID    FRUIT_NAME  COLOR   C
2   Apple   Red 2
1   Apple   Red 2
5   Orange  Orange  3
3   Orange  Orange  3
4   Orange  Orange  3