sqlserver和oracle中去除某一字段中重复字节，以逗号分隔
sqlserver：
create function reny_str(@str varchar(2000)) returns varchar(20)
as
begin
??? declare @t table(a varchar(200))
??? declare @b table(a varchar(200))
?????? insert @t
????? select substring(@str,number,charindex(',',@str + ',',number) - number)
?????? from master..stp_values
????? where type='p'
????????? and substring(',' + @str,number,1)=','
??? insert into @b select distinct a from @t
??? declare @a varchar(4000)
????? select? @a=isnull(@a+',','')+a from @b
??? return @a
end
?
select dbo.reny_str('游泳,游泳');
?
oracle:
CREATE OR REPLACE FUNCTION F_REPEAT(Report_No in varchar2) return varchar2 is
? StrReportNo? varchar2(2000);
? StrTemp????? varchar2(2000);
? TempReportNo varchar2(2000);
begin
? StrReportNo? := '';
? StrTemp????? := '';
? TempReportNo := Report_No;
? loop
??? --判断参数有没有值
??? if instr(TempReportNo || ',', ',', 1, 1) > 1 THEN
????? --以','分隔截取字段
????? StrTemp := SUBSTR(TempReportNo,
??????????????????????? 1,
??????????????????????? INSTR(TempReportNo ||',',',',1,1)-1);
??? end if;
??? --判断是否已经存在相同值,这里前后一定要加分隔符
??? if INSTR(',' || StrReportNo || ',', ',' || StrTemp || ',', 1, 1) = 0 THEN
????? --累加不同结果
????? StrReportNo := StrReportNo || ',' || StrTemp;
??? end IF;
??? --将判断过的字段删除掉
??? TempReportNo := SUBSTR(TempReportNo,
?????????????????????????? INSTR(TempReportNo || ',', ',', 1, 1) + 1);
??? --参数所有值完成过滤判断后跳出循环
??? exit WHEN length(TempReportNo) = 0 or TempReportNo is null;
? end LOOP;
? return SUBSTR(StrReportNo, 2);
? end F_REPEAT;
