ORACLE-delete repeated value functions in the same field

Sometimes we may need to filter the results of the query for repeated values. For example, we use the following SQL statement to query the results.

Statement:

Select SEQ_NO, REPORT_NO from Imp_Report

 

Result:

SEQ_NO REPORT_NO
-------------------------------
N3200000a091130000600 3200000109092905, 3200000109093647, 3200000109095568-3200000109093647
N3200000a091130000601 3200000109096320,320108109097222, 320410109097222

 

Expected results:

SEQ_NO REPORT_NO
-------------------------------
N3200000a091130000600 3200000109092905, 3200000109093647, 320410109095568
N3200000a091130000601 3200000109096320,3200000109097222

 

I wrote a function.

SQL statement

1 create or replace function FUN_IMP_REPORT_EXCLUDE_REPEAT (Report_No IN NVARCHAR2)
2 RETURN NVARCHAR2 IS
3 StrReportNo NVARCHAR2 (2000 );
4 strtemp nvarchar2 (2000 );
5 tempreportno nvarchar2 (2000 );
6 begin
7 strreportno: = '';
8 strtemp: = '';
9 tempreportno: = report_no;
10 Loop
11 -- determine whether the parameter has a value
12 if instr (tempreportno | ', 1, 1)> 1 then
13 -- separate fields with commas (,)
14 strtemp: = substr (tempreportno,
15 1,
16 instr (tempreportno | ',', 1, 1)-1 );
17 end if;
18 -- determine whether the same value already exists. A separator must be added before and after this
19 if instr (',' | strreportno | ',' | strtemp | ',', 1, 1) = 0 then
20 -- accumulate different results
21 strreportno: = strreportno | ',' | strtemp;
22 end if;
23 -- delete the field that has been judged
24 TempReportNo: = SUBSTR (TempReportNo,
25 INSTR (TempReportNo | ',', 1, 1) + 1 );
26 -- exit the loop after all values of the parameter are filtered and judged
27 exit when length (TempReportNo) = 0 or TempReportNo is null;
28 end loop;
29 return substr (StrReportNo, 2); -- In addition, it actually starts ','.
30 END FUN_IMP_REPORT_EXCLUDE_REPEAT;

The final SQL statement:

Select seq_no, fun_imp_report_exclude_repeat (report_no) from imp_report

Agoni 2009-12-29