By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test_data ( id, value ) AS
SELECT 1, 'agaga' FROM DUAL UNION ALL
SELECT 2, 'aaggcct' FROM DUAL UNION ALL
SELECT 3, 'aaggcctt' FROM DUAL UNION ALL
SELECT 4, 'aaagggcccttt' FROM DUAL;
4 rows affected
WITH substrings ( id, value, length, pos ) AS (
SELECT id,
value,
LENGTH( value ),
1
FROM test_data
UNION ALL
SELECT id,
value,
CASE pos
WHEN 1
THEN length - 1
ELSE length
END,
CASE pos
WHEN 1
THEN LENGTH(value) - (length-2)
ELSE pos-1
END
FROM substrings
WHERE length > 1
OR pos > 1
),
non_repeats ( id, value, substring ) AS (
SELECT id,
MIN( value ),
SUBSTR( value, pos, length )
FROM substrings s
GROUP BY id, SUBSTR( value, pos, length )
HAVING COUNT(*) = 1
)
SELECT id,
value,
substring
FROM (
SELECT id,
| ID | VALUE | SUBSTRING |
|---|---|---|
| 1 | agaga | gag |
| 2 | aaggcct | t |
| 3 | aaggcctt | gc |
| 3 | aaggcctt | cc |
| 3 | aaggcctt | ct |
| 3 | aaggcctt | ag |
| 3 | aaggcctt | aa |
| 3 | aaggcctt | tt |
| 3 | aaggcctt | gg |
| 4 | aaagggcccttt | ct |
| 4 | aaagggcccttt | gc |
| 4 | aaagggcccttt | ag |