有一字符串是:4683,968
有一字段id是number型
现在要把4683,968转换成number型,来进行id in (4683,968)的判断,
但是select to_number('4683,968') from dual会报错,因为逗号为无效数字,
现在用什么方法才能转换用以比较呢?郁闷求解。
有一字段id是number型
现在要把4683,968转换成number型,来进行id in (4683,968)的判断,
但是select to_number('4683,968') from dual会报错,因为逗号为无效数字,
现在用什么方法才能转换用以比较呢?郁闷求解。
如果是为了id in (4683,968)判断,好像没有必要转换的啊。你能组成一个SQL语句再执行就可以。
select substr('4683,968',1,instr('4683,968',',',1)-1) id from dual
union all
select substr('4683,968',instr('4683,968',',',1)+1,length('4683,968')) id from dual)
就会出现:
4683
968,969
968和969中间还是有逗号的。
select ''''||replace(V_IN_ID,'$',''',''')||'''' into list from dual;
select replace(V_IN_ID,'$',''',''') into list from dual;三种方法都试过了,还是不行。大哥大姐帮我出出主意啊。
select ''''||replace('4683,968,969',',',''',''')||'''' from dual;
这个我都试过了,怎么不行呢?
declare i integer;
temp1 varchar2(20);
v1 varchar2(20);
v2 varchar2(20);
v_sql varchar2(20);
begin
v1:='4683$968$969';
select ''''||replace(v1,'$',''',''')||'''' into temp1 from dual;
dbms_output.put_line(temp1);
end;输出:'4683','968','969'
经过转换是不是最后变成了id in (''4683','968','969'')?
select 4683 id from dual
union
select 968 id from dual
union
select 969 id from dual
..........
这个循环拼接的sql怎么写啊?
select 4683 id from dual
union
select 968 id from dual
union
select 969 id from dual
..........
这个循环拼接的sql怎么写啊?
SELECT (select 4683 id from dual)||(select 968 id from dual)||(select 969 id from dual) ID FROM DUAL;
select to_number(replace('4683,968',',','')) from dual;
hongqi162(失踪的月亮) 说的非常对~~~~~