工作要求对客户表dclient表fax字段进行清洗,要求字段中只含有数字。
字段中有很多数据,
fax
FAX-87053000
FAX-85250208
FAX-7083488
7074837(F)
FAX-8810004
FAX-5170062
88060687FAX
85304470(张
88060687FAX
等数据,现要求取只取数字的结果,即查询结果为:
87053000
85250208
7083488
8810004
5170062
88060687
85304470
88060687
我按照网上的朋友提供的思路作出了存储过程,但是不能达到预期的效果,请各位朋友帮忙看看,我存储过程是这样的:
create or replace procedure test3 is
v_fax t97.dclient.fax%type;
bb varchar2(100);
dd varchar2(100);
i number(10);
cursor mycursor is
select fax from t97.dclient where rownum<100;begin
bb:='';
dd:='';
open mycursor;
loop
fetch mycursor into v_fax;
for i in 0..length(v_fax) loop
bb:=substr(v_fax,i,1);
--dbms_output.put_line(bb);
if(ascii(bb)>=48 and ascii(bb)<=57)then
dd:=bb||dd;
end if;
end loop;
dbms_output.put_line(dd);
--v_fax:=dd;
--dbms_output.put_line('fax is'||v_fax);
exit when mycursor%notfound;
end loop;
close mycursor;
end ;
单步跟踪发现for i in 0..length(v_fax) loop这条语句有问题,但我还是搞不定,请各位朋友帮忙
字段中有很多数据,
fax
FAX-87053000
FAX-85250208
FAX-7083488
7074837(F)
FAX-8810004
FAX-5170062
88060687FAX
85304470(张
88060687FAX
等数据,现要求取只取数字的结果,即查询结果为:
87053000
85250208
7083488
8810004
5170062
88060687
85304470
88060687
我按照网上的朋友提供的思路作出了存储过程,但是不能达到预期的效果,请各位朋友帮忙看看,我存储过程是这样的:
create or replace procedure test3 is
v_fax t97.dclient.fax%type;
bb varchar2(100);
dd varchar2(100);
i number(10);
cursor mycursor is
select fax from t97.dclient where rownum<100;begin
bb:='';
dd:='';
open mycursor;
loop
fetch mycursor into v_fax;
for i in 0..length(v_fax) loop
bb:=substr(v_fax,i,1);
--dbms_output.put_line(bb);
if(ascii(bb)>=48 and ascii(bb)<=57)then
dd:=bb||dd;
end if;
end loop;
dbms_output.put_line(dd);
--v_fax:=dd;
--dbms_output.put_line('fax is'||v_fax);
exit when mycursor%notfound;
end loop;
close mycursor;
end ;
单步跟踪发现for i in 0..length(v_fax) loop这条语句有问题,但我还是搞不定,请各位朋友帮忙
bb:=substr(v_fax,i,1);
---------------------------
错在这里,i应该从1开始循环,如果i从0开始循环的话substr(v_fax,0,1)和substr(v_fax,1,1)都是取子串的第一个字符这个问题可以用translate函数嵌套方式解决,比如楼主的问题可以这样:SELECT translate(fax,translate(fax,'0123456789','#'),'#') FROM dclient;这里参数'#'可以替换成fax字段里不会出现的任意字符,或者替换成chr(30),这个字符不能通过普通录入方式插入的,应该不会出现在fax字段中
v_fax t97.dclient.fax%type;
bb varchar2(100);
dd varchar2(100);
i number(10);
vi_rowcount number(10);
cursor mycursor is
select fax from t97.dclient ;
begin
vi_rowcount := 0;
bb:='';
dd:='';
open mycursor;
loop
fetch mycursor into v_fax;
for i in 1..length(v_fax) loop
bb:=substr(v_fax,i,1);
if(ascii(bb)>=48 and ascii(bb)<=57)then
dd:=bb||dd;
end if;
end loop;
vi_rowcount := vi_rowcount + 1;
dbms_output.put_line(vi_rowcount||':'||dd);
exit when mycursor%notfound;
end loop;
close mycursor;
end ;
这样可以吗?
AS
CURR_CHAR VARCHAR2(3);
rtVal VARCHAR2(4000);
BEGIN
rtVal:=NULL; IF theStr IS NOT NULL THEN
FOR i IN 1..LENGTH(theStr) LOOP
CURR_CHAR:=SUBSTR(theStr,i,1);
--ASCII(0..9)=48..57
IF ASCII(CURR_CHAR) BETWEEN 48 AND 57 THEN
rtVal:=rtVal || CURR_CHAR;
END IF;
END LOOP;
END IF;
RETURN rtVal;
EXCEPTION
WHEN OTHERS THEN RETURN NULL;
END;
/
SQL> SELECT GET_NUMBER('&STR') FROM DUAL;
输入 str 的值: 010-12345678
原值 1: SELECT GET_NUMBER('&STR') FROM DUAL
新值 1: SELECT GET_NUMBER('010-12345678') FROM DUALGET_NUMBER('010-12345678')
---------------------------------------------------------
01012345678SQL> SELECT GET_NUMBER('&STR') FROM DUAL;
输入 str 的值: 12345678(梦霄)
原值 1: SELECT GET_NUMBER('&STR') FROM DUAL
新值 1: SELECT GET_NUMBER('12345678(梦霄)') FROM DUALGET_NUMBER('12345678(梦霄)')
---------------------------------------------------------
12345678然后就可以直接更新你那个字段了:UPDATE dclient SET fax=GET_NUMBER(fax) WHERE NVL(fax,'X')<>NVL(GET_NUMBER(fax),'X');注意事项:
1、函数虽然要返回纯数字,但不能 RETURN NUMBER,因为这样一来 010 前面的0就没有了
2、UPDATE 的时候,WHERE子句中的 NVL 必不可少,有可能出现一个 fax内容全是汉字,这样函数返回值是NULL,如果不用 NVL,则该行就不能成功清洗
v_fax t97.dclient.fax%type;
bb varchar2(100);
dd varchar2(100);
i number(10);
vi_len number(10);
vi_rowcount number(10);
cursor mycursor is
select fax from t97.dclient ;
begin
vi_rowcount := 0;
bb:='';
dd:='';
open mycursor;
loop
fetch mycursor into v_fax;
vi_len := length(v_fax);
for i in 1..vi_len loop
bb:=substr(v_fax,i,1);
if(ascii(bb)>=48 and ascii(bb)<=57)then
dd:=bb||dd;
end if;
end loop;
vi_rowcount := vi_rowcount + 1;
dbms_output.put_line(vi_rowcount||':'||dd);
exit when mycursor%notfound;
end loop;
close mycursor;
end ;未必还会有问题?
从而可以使用如下语句检测一个字符串中是否存在非汉字字符:
SELECT COUNT(*) FROM Yourtab WHERE TO_MULTI_BYTE(col)<>TO_SINGLE_BYTE(col);现在dclient表fax字段全都是汉字的只有2个,我直接就可以更新啦,你说的1、函数虽然要返回纯数字,但不能 RETURN NUMBER,因为这样一来 010 前面的0就没有了,恕我愚钝,没明白你说的什么意思?