Remove the duplicated record

Bilal Khan · **Joined:** Sat Mar 13, 2010 7:03 am **Posts:** 18

I want to apply divide and conquer approach on the field value,
For example I have the field value like this

Value
32,36,12,65,85,9663
43,36,89,65,112,9663
36,63,12,65,95,123
32,36,12,85, 85,9663

Remember that these value are taken from ONLY one field i.e Value.
Apply the divide and conquer approach in such a manner that take the value of a single field and each row and divide the value into two half. Continue this process until single value until we reach the single value or single sub problems.
Apply the divide and conquer approach on all the value of field. When we get final value then match the value of two different rows. And check how much field value are similar (match or duplicated). If field values are match more then 80% then delete the duplicated value and keep the original entity.
Please help me in this regards!
Thanks
i have following code but i cant do it,

Code:

WITH t AS (
    SELECT rowid AS rid
    ,      STRING_TO_TABLE(value) AS vals
    FROM   tbl
)
SELECT t1.vals AS v1
,      t2.vals AS v2
FROM t t1
JOIN t t2 ON t1.rid < t2.rid
 
 
--Perform a cartesian product of the table
 
WITH t AS (
    SELECT rowid AS rid
    ,      STRING_TO_TABLE(value) AS vals
    FROM   tbl
)
SELECT t1.vals AS v1
,      t2.vals AS v2
,      t1 MULTISET INTERSECT DISTINCT t2 AS t1_2
FROM t t1
JOIN t t2 ON t1.rid < t2.rid
 
 
WITH t AS (
    SELECT rowid AS rid
    ,      STRING_TO_TABLE(value) AS vals
    FROM   tbl
)
 
--Use the MULTISET INTERSECT operator to combine the two sets
 
SELECT v1, v2, v_1_2
FROM (
    SELECT t1.vals AS v1
    ,      t2.vals AS v2
    ,      t1.vals MULTISET INTERSECT DISTINCT t2.vals AS v1_2
    FROM t t1
    JOIN t t2 ON t1.rid < t2.rid
)
WHERE CARDINALITY(v_1_2) > 
      0.8 * GREATEST(CARDINALITY(v1), CARDINALITY(v1))
 
 

thats why i need a that function that converts comma-separated list of values into a Nested Table.

So help me in this regards. Thanks

usman · **Joined:** Fri Feb 13, 2009 11:37 am **Posts:** 118

Hi,
I will work on it soon and wil let you know
however to put in a collection you can do it like this

Code:

DECLARE
  lv_Str_List          VARCHAR2(1000) := '32,36,12,65,85,9663';
  lb_cnt                BINARY_INTEGER;
  la_Tab_Str          DBMS_UTILITY.UNCL_ARRAY;
  lv_Str_List_Quote VARCHAR2(3000);
  
BEGIN
  -- put all the strings in double quotes to avoid special character problems 
  -- while parsing with comma_to_table procedure
  lv_Str_List_Quote := '"' || REPLACE( lv_Str_List, ',', '","' ) || '"';
 
  -- parse the string into comma separated table
  DBMS_UTILITY.COMMA_TO_TABLE(lv_Str_List_Quote, lb_cnt, la_Tab_Str);
 
  FOR i IN 1 .. la_Tab_Str.COUNT LOOP
    -- remove double quotes added earlier and trim to fetch the actual string
    DBMS_OUTPUT.PUT_LINE(TRIM(REPLACE( la_Tab_Str(i), '"', '' )));
    --list1.extend;
    list1(i):=TRIM(REPLACE( la_Tab_Str(i), '"', '' ));
  END LOOP;
END;

Bilal Khan · **Joined:** Sat Mar 13, 2010 7:03 am **Posts:** 18

Thanks Sir.
But sir DBMS_UTILITY have a lot of limitations, thats why plz use any other function.
Once again Thanks.

craig · **Joined:** Tue Feb 10, 2009 5:18 pm **Posts:** 77

If you have APEX installed in your database, there is a function that might assist

Code:

STRING_TO_TABLE Function

Given a string, this function returns a PL/SQL array of type APEX_APPLICATION_GLOBAL.VC_ARR2. This array is a VARCHAR2(32767) table.

Syntax

APEX_UTIL.STRING_TO_TABLE (
    p_string       IN VARCHAR2,
    p_separator    IN VARCHAR2 DEFAULT ':') 
    RETURN APEX_APPLICATION_GLOBAL.VC_ARR2;

Remove the duplicated record

Who is online