S.NO id Pid 1 123 PAQ123 2 433 WSD3FF 3 565 PAS45E 4 123 PAQ123X 5 433 WSD3FFY 6 123 PAQ123Z
suppose the above is the sample records in the database.
Now I want to find out in the database whether there is any word (example PAQ123) which is repeating with some prefixes/suffixes like in (PAQ123X,PAQ123Z).
How can I write a query which would result into the above list scenario?
Oracle Setup:
CREATE TABLE table_name ( S_NO, id, Pid ) AS
SELECT 1, 123, 'PAQ123' FROM DUAL UNION ALL
SELECT 2, 433, 'WSD3FF' FROM DUAL UNION ALL
SELECT 3, 565, 'PAS45E' FROM DUAL UNION ALL
SELECT 4, 123, 'PAQ123X' FROM DUAL UNION ALL
SELECT 5, 433, 'WSD3FFY' FROM DUAL UNION ALL
SELECT 6, 123, 'PAQ123Z' FROM DUAL;
Query:
SELECT *
FROM (
SELECT t.*,
( SELECT COUNT(*)
FROM table_name x
WHERE t.id = x.id
AND LENGTH( t.Pid ) < LENGTH( x.pid )
AND INSTR( x.Pid, t.Pid ) = 1 ) AS num_matches
FROM Table_name t
)
WHERE num_matches > 0;
Output:
S_NO ID PID NUM_MATCHES
---------- ---------- ------- -----------
1 123 PAQ123 2
2 433 WSD3FF 1
If you want to get the matches then you can use a collection:
CREATE TYPE stringlist AS TABLE OF VARCHAR2(100);
/
Query:
SELECT *
FROM (
SELECT t.*,
CAST(
MULTISET(
SELECT PID
FROM table_name x
WHERE t.id = x.id
AND LENGTH( t.Pid ) < LENGTH( x.pid )
AND INSTR( x.Pid, t.Pid ) = 1
)
AS stringlist
) AS matches
FROM Table_name t
)
WHERE matches IS NOT EMPTY;
or (since I'm not sure MULTISET is in 10g):
SELECT *
FROM (
SELECT t.*,
CAST(
(
SELECT COLLECT( PID )
FROM table_name x
WHERE t.id = x.id
AND LENGTH( t.Pid ) < LENGTH( x.pid )
AND INSTR( x.Pid, t.Pid ) = 1
)
AS stringlist
) AS matches
FROM Table_name t
)
WHERE matches IS NOT EMPTY;
Output
S_NO ID PID MATCHES
---------- ---------- ------- ------------------------------------
1 123 PAQ123 TEST.STRINGLIST('PAQ123X','PAQ123Z')
2 433 WSD3FF TEST.STRINGLIST('WSD3FFY')
