create table t1 (c1 varchar2(5));insert into t1 values ('11'); insert into t1 values (''); insert into t1 values ('123'); commit;select c1+1 c1 from t1 c1 ------------------ 1 12 2 3 124
-- 既然只有部分数据是数字,也就是说该字段存在非数字的值,所以你应该写个函数,将判断某行该字段是否是合法的数值,如果是合法的数值,则进行相关的数值运算。-- 例如:先写个函数,用于判断某字符是否是数值(是数值,则返回1,否则返回0): create or replace function is_number(i_str varchar2) return number is v_number number(18,0); begin select to_number(i_str) into v_number from dual; if v_number is not null then return 1; else return 0; end if; EXCEPTION WHEN OTHERS THEN return 0; end; /-- 然后在你要执行数值运行的时候,where条件中调用该函数,例如: update table, set c1=c1+1 where is_number(c1)=1;
哦 看错了 有部分字符和数字 现在只计算是数字的?create table t1 (c1 number(5),c2 varchar2(5));insert into t1 values (1,'11'); insert into t1 values (2,'a12'); insert into t1 values (3,'123'); insert into t1 values (4,'sss'); insert into t1 values (5,'02'); commit;select c1, c2+1 c2 from t1 where regexp_like(c2,'^[0-9]*$') c1 c2 ----------------------- 1 1 12 2 3 124 3 5 3
with tb as (select 1 id,'s' name from dual union all select 1 id,'123s' name from dual union all select 2,'123' name from dual) select id, case when regexp_instr(name,'[[:alpha:]]')>0 then 0 else name+0 end+id from tb
with tb as (select 1 id,'s' name from dual union all select 1 id,'123s' name from dual union all select 1 id,'12 3' name from dual union all select 1 id,'12?3' name from dual union all select 2,'123' name from dual) select translate(name,'/0123456789','/'),case when translate(name,'/0123456789','/') is null then to_number(name) else 0 end+id from tb补助楼上的漏洞多谢罗哥提醒空格等其他字符等漏洞问题
判断是否数字的条件还要加上一条 not like ‘%.%.%’
with tb as (select 1 id,'s' name from dual union all select 1 id,' ' name from dual union all select 1 id,null name from dual union all select 1 id,'s.s' name from dual union all select 1 id,'123s' name from dual union all select 1 id,'12 3' name from dual union all select 1 id,'12?3' name from dual union all select 1 id,'12.3' name from dual union all select 1 id,'12。3' name from dual union all select 1 id,'12..3' name from dual union all select 1 id,'我.你' name from dual union all select 2,'123' name from dual) select translate(name,'/0123456789','/'),case when translate(name,'/0123456789','/') is null or translate(name,'/0123456789','/')='.' then to_number(name) else 0 end+id from tb其实觉得还是直接自己定义个函数来写luoyoumou 的我的最初只想通过SQL 来解决
create table t1 (c1 varchar2(5));insert into t1 values ('11');
insert into t1 values ('');
insert into t1 values ('123');
commit;select c1+1 c1
from t1
c1
------------------
1 12
2
3 124
create or replace function is_number(i_str varchar2)
return number
is
v_number number(18,0);
begin
select to_number(i_str) into v_number from dual;
if v_number is not null then
return 1;
else
return 0;
end if;
EXCEPTION
WHEN OTHERS
THEN
return 0;
end;
/-- 然后在你要执行数值运行的时候,where条件中调用该函数,例如:
update table, set c1=c1+1
where is_number(c1)=1;
insert into t1 values (2,'a12');
insert into t1 values (3,'123');
insert into t1 values (4,'sss');
insert into t1 values (5,'02');
commit;select c1, c2+1 c2
from t1
where regexp_like(c2,'^[0-9]*$') c1 c2
-----------------------
1 1 12
2 3 124
3 5 3
(select 1 id,'s' name from dual union all
select 1 id,'123s' name from dual union all
select 2,'123' name from dual)
select id, case when regexp_instr(name,'[[:alpha:]]')>0 then 0 else name+0 end+id from tb
(select 1 id,'s' name from dual union all
select 1 id,'123s' name from dual union all
select 1 id,'12 3' name from dual union all
select 1 id,'12?3' name from dual union all
select 2,'123' name from dual)
select translate(name,'/0123456789','/'),case when translate(name,'/0123456789','/') is null then to_number(name) else 0 end+id from tb补助楼上的漏洞多谢罗哥提醒空格等其他字符等漏洞问题
with tb as
(select 1 id,'s' name from dual union all
select 1 id,' ' name from dual union all
select 1 id,null name from dual union all
select 1 id,'s.s' name from dual union all
select 1 id,'123s' name from dual union all
select 1 id,'12 3' name from dual union all
select 1 id,'12?3' name from dual union all
select 1 id,'12.3' name from dual union all
select 1 id,'12。3' name from dual union all
select 1 id,'12..3' name from dual union all
select 1 id,'我.你' name from dual union all
select 2,'123' name from dual)
select translate(name,'/0123456789','/'),case when translate(name,'/0123456789','/') is null or translate(name,'/0123456789','/')='.' then to_number(name) else 0 end+id from tb其实觉得还是直接自己定义个函数来写luoyoumou 的我的最初只想通过SQL 来解决