oracle去掉重复的字段,ORACLE有函数可以去掉字段里面的重复字符吗？

-- http://www.itpub.net/viewthread. ... p;extra=&page=1

字符串去冗余，请教

有字符串'ABCD6F6CAAX'

要求得到 'ABCD6FX'

即对于重复的字符，只保留一个

谢谢

create or replace function f(pstr in varchar2) return varchar2

is

v_newstr varchar2(100) := null;

i pls_integer:=1;

begin

for i in 1..length(pstr) loop

if instr(v_newstr,substr(pstr,i,1))<=0 or v_newstr is null then

v_newstr :=v_newstr||substr(pstr,i,1);

end if;

end loop;

return v_newstr;

end;

/

-- 根据10楼的代码，现在我真的很质疑 Connect by 的效率了：

-- 下面是两个函数(实现同样的功能：将一串字符串去重，只顺序选择第一次出现的字符)

-- 其中：f1函数是用的connect by，然后循环追加再输出；

f2用的是直接循环中if判断(10楼写的函数)；

-- 函数 f1

CREATE OR REPLACE FUNCTION f1(i_str in varchar2)

RETURN varchar2

IS

v_str VARCHAR2(4000);

CURSOR cur is

with a as (select level as lv, substr(i_str,level, 1) as substrs

from dual

connect by level<=length(i_str) )

select a1.lv, a1.substrs

from a a1

where not exists (select 1

from a a2

where a2.lv

order by a1.lv;

BEGIN

v_str := '';

FOR i in cur LOOP

v_str:=v_str||i.substrs;

END LOOP;

RETURN v_str;

END;

/

-- 函数 f2

create or replace function f2(pstr in varchar2) return varchar2

is

v_newstr varchar2(4000) := null;

i pls_integer:=1;

begin

for i in 1..length(pstr) loop

if instr(v_newstr,substr(pstr,i,1))<=0 or v_newstr is null then

v_newstr :=v_newstr||substr(pstr,i,1);

end if;

end loop;

return v_newstr;

end;

/

-- 效率测试：

CREATE TABLE t(

id number(18,0),

random_str varchar2(40)

);

-- 先随机插入10万条数据

BEGIN

FOR i IN 1..100000 LOOP

INSERT INTO t(id, random_str)

SELECT i, dbms_random.string('U',1)||dbms_random.string('L',39) from dual;

IF mod(i,100)=0 THEN

COMMIT;

END IF;

END LOOP;

COMMIT;

END;

/

-- 创建 t1 表，用来测试函数f1

CREATE TABLE t1 as select * from t where 1=2;

-- 创建 t2 表，用来测试函数f2

CREATE TABLE t2 as select * from t where 1=2;

-- 循环测试函数f1

DECLARE

v_begin_test date;

v_end_test date;

v_seconds number(18,0);

BEGIN

SELECT sysdate into v_begin_test from DUAL;

FOR i in(select id, random_str from t order by id) LOOP

insert into t1(id, random_str)

select i.id, f1(i.random_str) from dual;

IF mod(i.id,100)=0 THEN

COMMIT;

END IF;

END LOOP;

SELECT sysdate into v_end_test from DUAL;

SELECT (v_end_test-v_begin_test)*24*60*60 INTO v_seconds FROM DUAL;

DBMS_OUTPUT.PUT_LINE('Test function f1 ... '||chr(10)||'Begin test time: '||

to_char(v_begin_test,'YYYY-MM-DD HH24:MI:SS')||chr(10)||'  End test time: '||

to_char(v_end_test,'YYYY-MM-DD HH24:MI:SS')||chr(10)||

'Test times(Seconds): '||to_char(v_seconds));

COMMIT;

END;

/

Test function f1 ...

Begin test time: 2011-06-14 11:02:35

End test time: 2011-06-14 11:07:19

Test times(Seconds): 284

PL/SQL 过程已成功完成。

-- 循环测试函数f2

DECLARE

v_begin_test date;

v_end_test date;

v_seconds number(18,0);

BEGIN

SELECT sysdate into v_begin_test from DUAL;

FOR i in(select id, random_str from t order by id) LOOP

insert into t2(id, random_str)

select i.id, f2(i.random_str) from dual;

IF mod(i.id,100)=0 THEN

COMMIT;

END IF;

END LOOP;

COMMIT;

SELECT sysdate into v_end_test from DUAL;

SELECT (v_end_test-v_begin_test)*24*60*60 INTO v_seconds FROM DUAL;

DBMS_OUTPUT.PUT_LINE('Test function f2 ... '||chr(10)||'Begin test time: '||

to_char(v_begin_test,'YYYY-MM-DD HH24:MI:SS')||chr(10)||'  End test time: '||

to_char(v_end_test,'YYYY-MM-DD HH24:MI:SS')||chr(10)||

'Test times(Seconds): '||to_char(v_seconds));

END;

/

Test function f2 ...

Begin test time: 2011-06-14 11:10:04

End test time: 2011-06-14 11:10:11

Test times(Seconds): 7

PL/SQL 过程已成功完成。