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
