CODEWITHSUNDEEP
sqloracle-databaseformat
Sky
Sky

Reputation: 3360

Oracle PL SQL remove duplicate data in string

.....
temp Varchar2 (20);
e_name Varchar2 (255);
.....

Begin

e_name := e_name || temp;

Dbms_Output.Put_Line('names: ' || e_name);

Output result
-------------
names: 'John', 'Sam', 'David', 'Sam', 'John', 'Alex'

How do I format my e_name to remove the duplicate names so that I have the output result

required result
-------------
names: 'John', 'Sam', 'David', 'Alex'

Upvotes: 1

Views: 10951

Answers (4)

Art
Art

Reputation: 5792

Replace WM_CONCAT with LISTAGG - I'm running Oragle 10g, LISTAGG is 11g:

SELECT wm_concat(ename) AS employees
  FROM emp_test
 WHERE deptno = 20
/

 Output - SMITH repeats twice:
    SMITH,JONES,SCOTT,ADAMS,FORD,SMITH

SELECT wm_concat(distinct ename) AS employees
 FROM emp_test
WHERE deptno = 20
/

The distinct fixes the problem:
  ADAMS,FORD,JONES,SCOTT,SMITH

Upvotes: 1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

There is something called ASSOCIATIVE array,which act as Hash table in Java. We can put delimted text into a hash table, and thus can eliminate the duplicates.

We use EXISTS method of collection here, to check if the value is already present!

This is a simple to read NO SQL type of solution. So, performing one.

DECLARE
   e_name VARCHAR2 (4000) := 'the text goes here';
   L_TEMP_TEXT  VARCHAR2(4000);
   V_LOOPCOUNT NUMBER :=0;
   T_WORD VARCHAR2(4000);
   T_FINAL_TEXT VARCHAR2(4000) := ' ';

   --Declare a DICT like a Hash table
   TYPE DICT IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR(4000);
   MYDICT DICT;
BEGIN
      L_TEMP_TEXT := regexp_replace(e_name,'[,]+',','); -- Replace multiple consecutive commas as single
      LOOP
        v_LOOPCOUNT := v_LOOPCOUNT+1;
        T_WORD      := REGEXP_SUBSTR(e_name, '[^,]+', 1, V_LOOPCOUNT);
        --In a loop we tokenize the String using comma as delimiter
        EXIT WHEN T_WORD IS NULL;

        IF NOT MYDICT.EXISTS(T_WORD) THEN
          -- It is like a Hash Table, if not exists add to it.
           MYDICT(T_WORD) := T_WORD;
           T_FINAL_TEXT : T_FINAL_TEXT || ',' || T_WORD;
        END;
      END LOOP;
      T_FINAL_TEXT := TRIM(BOTH ',' FROM TRIM(T_FINAL_TEXT));
      -- Trimming the unwanted commas

      DBMS_OUTPUT.PUT_LINE('after removing duplicates : ' || T_FINAL_TEXT);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(sqlerrm||chr(10)||dbms_utility.format_error_backtrace);
END;
/

Upvotes: 0

KevinKirkpatrick
KevinKirkpatrick

Reputation: 1456

This can be done in pure SQL, but it gets a bit messy, especially if you're pre-11gR2 listagg(). Since you're already in PL/SQL territory, here's a solution that eliminates the dups in a simple PL/SQL fashion:

declare e_name varchar2(255) := q'"'John', 'Sam', 'David', 'Sam', 'John', 'Alex'"';
        new_ename varchar2(255) := substr(e_name,1,instr(e_name,'''',2));
begin
dbms_output.put_line ('e_name: ' || e_name);
for i in 1..length(e_name) - length(replace(e_name,',')) loop
    if instr(new_ename,
        substr(e_name,instr(e_name,', ',1,i),instr(e_name||', ',', ',1,i+1) - instr(e_name,', ',1,i))) = 0
    then 
        new_ename := new_ename || substr(e_name,instr(e_name,', ',1,i),instr(e_name||', ',', ',1,i+1) - instr(e_name,', ',1,i));
    end if;
end loop;
dbms_output.put_line ('new_ename: ' || new_ename);
end;


e_name: 'John', 'Sam', 'David', 'Sam', 'John', 'Alex'
new_ename: 'John', 'Sam', 'David', 'John', 'Alex'

Upvotes: 0

San
San

Reputation: 4538

Well you can modify your program like this

Begin
e_name := e_name || temp;
 SELECT listagg ( names, ',' ) within GROUP (ORDER BY rn )
   INTO e_name
   FROM
  (
         SELECT level rn,
                regexp_substr ( e_name, '[^,]+', 1, level ) names,
                row_number ( ) over ( partition BY regexp_substr ( e_name, '[^,]+', 1, level ) order by level ) rnn
           FROM dual
          CONNECT BY regexp_substr ( e_name, '[^,]+', 1, level ) IS NOT NULL
  )
  WHERE rnn = 1;

Dbms_Output.Put_Line('names: ' || e_name);

The inner most query will convert the list into rows and then outer queries will filter and create the string again.

Upvotes: 1

Related Questions