SQL> select * from test;TESTTIME RTIME ---------- ---------- 1.2.13 2.343.23 23.4334.54--我是用replace把.去掉了,如果你的字段里只包含.的话应该是可以的 SQL> select replace(testtime,'.','') from test;REPLACE(TE ---------- 1213 234323 23433454
select replace(your_column, '.', '') from your_table;
select replace(column_name, '.', '') from table_name
select replace(column, '.', '') from table
顶楼上的! 数字字串最终转换成number要+0: SELECT REPLACE('2312321.431432.1312','.','')+0 FROM dual;
sql>select replace('2312321.431432.1312;321312.432432.1312;312123.1321','.','') from tb
select replace('2312321.431432.1312;321312.432432.1312;312123.1321','.','')+0 from tb
SQL> select * from t_trans;
ID NAME ----------- -------------------------------------------------------------------------------- 1 2312321.431432.1312;321312.432432.1312;312123.1321
SQL> select id, translate(name,'1234567890'||name,'1234567890') from t_trans;
ID TRANSLATE(NAME,'1234567890'||N ----------- -------------------------------------------------------------------------------- 1 2312321431432131232131243243213123121231321
SQL>
莫非楼主是这意思? SQL> select * from t_trans;
ID NAME ----------- -------------------------------------------------------------------------------- 1 2312321.431432.1312;321312.432432.1312;312123.1321
SQL> SQL> select a.id,replace(substr(';'||name||';',instr(';'||name||';',';',1,rn)+1,instr(';'||name||';',';',1,rn+1)-instr(';'||name||';',';',1,rn)-1),'.','') 2 from t_trans a,(select rownum rn from dual connect by rownum <=(select max(length(translate(name,';'||name,';'))) from t_trans))t1 3 ;
ID REPLACE(SUBSTR(';'||NAME||';', ----------- -------------------------------------------------------------------------------- 1 23123214314321312 1 3213124324321312 1 3121231321
SQL>
: --大家都误解楼主的意思了!两个点的要保留第二个点再转成number行 --table create table test_t(id varchar(100));--data-- insert into tset_t values('321312.432432.1312') insert into tset_t values( '2312321.431432.1312') insert into tset_t values('312123.1321')--sql statement-- select substr(id,'1',instr(id,'.','1','1')-1)|| substr(id,instr(id,'.','1','1')+1,100) newid from test_t--results-- newid 2312321431432.1312 4314321312 321312432432.1312
--注意+0select substr(id,'1',instr(id,'.','1','1')-1)|| substr(id,instr(id,'.','1','1')+1,100)+0 newid from test_t
replace(col,'.','')+0
SQL> select id,replace(substr(num,1,instr(num,'.',-1,1)-1),'.','')||substr(num,instr(num,'.',-1,1)) 2 from( 3 select a.id,substr(';'||name||';',instr(';'||name||';',';',1,rn)+1,instr(';'||name||';',';',1,rn+1)-instr(';'||name||';',';',1,rn)-1) num 4 from t_trans a,(select rownum rn from dual connect by rownum <=(select max(length(translate(name,';'||name,';'))) from t_trans))t1 5 );
ID REPLACE(SUBSTR(NUM,1,INSTR(NUM ----------- -------------------------------------------------------------------------------- 1 2312321431432.1312 1 321312432432.1312 1 312123.1321
SQL> select * from test;TESTTIME RTIME
---------- ----------
1.2.13
2.343.23
23.4334.54--我是用replace把.去掉了,如果你的字段里只包含.的话应该是可以的
SQL> select replace(testtime,'.','') from test;REPLACE(TE
----------
1213
234323
23433454
数字字串最终转换成number要+0:
SELECT REPLACE('2312321.431432.1312','.','')+0 FROM dual;
ID NAME
----------- --------------------------------------------------------------------------------
1 2312321.431432.1312;321312.432432.1312;312123.1321
SQL> select id, translate(name,'1234567890'||name,'1234567890') from t_trans;
ID TRANSLATE(NAME,'1234567890'||N
----------- --------------------------------------------------------------------------------
1 2312321431432131232131243243213123121231321
SQL>
SQL> select * from t_trans;
ID NAME
----------- --------------------------------------------------------------------------------
1 2312321.431432.1312;321312.432432.1312;312123.1321
SQL>
SQL> select a.id,replace(substr(';'||name||';',instr(';'||name||';',';',1,rn)+1,instr(';'||name||';',';',1,rn+1)-instr(';'||name||';',';',1,rn)-1),'.','')
2 from t_trans a,(select rownum rn from dual connect by rownum <=(select max(length(translate(name,';'||name,';'))) from t_trans))t1
3 ;
ID REPLACE(SUBSTR(';'||NAME||';',
----------- --------------------------------------------------------------------------------
1 23123214314321312
1 3213124324321312
1 3121231321
SQL>
--table
create table test_t(id varchar(100));--data--
insert into tset_t values('321312.432432.1312')
insert into tset_t values( '2312321.431432.1312')
insert into tset_t values('312123.1321')--sql statement--
select substr(id,'1',instr(id,'.','1','1')-1)||
substr(id,instr(id,'.','1','1')+1,100) newid from test_t--results--
newid
2312321431432.1312
4314321312
321312432432.1312
--注意+0select substr(id,'1',instr(id,'.','1','1')-1)||
substr(id,instr(id,'.','1','1')+1,100)+0 newid from test_t
2 from(
3 select a.id,substr(';'||name||';',instr(';'||name||';',';',1,rn)+1,instr(';'||name||';',';',1,rn+1)-instr(';'||name||';',';',1,rn)-1) num
4 from t_trans a,(select rownum rn from dual connect by rownum <=(select max(length(translate(name,';'||name,';'))) from t_trans))t1
5 );
ID REPLACE(SUBSTR(NUM,1,INSTR(NUM
----------- --------------------------------------------------------------------------------
1 2312321431432.1312
1 321312432432.1312
1 312123.1321
SQL>
CODE GRADE
-------------------- ----------------------
01
0.10.1
0.2
02.01
02.02
02.02.01
020.202
020.2020.1
0203
02.0202010.10101
10 rows selected
SQL> update t2 set grade=replace(SUBSTR(CODE,1,INSTR(CODE,'.',-1,1)-1),'.','')|| SUBSTR(CODE,INSTR(CODE,'.',-1,1));
10 rows updated
SQL> SELECT * from t2;
CODE GRADE
-------------------- ----------------------
01 1.00000
0.10.1 10.10000
0.2 0.20000
02.01 2.01000
02.02 2.02000
02.02.01 202.01000
020.202 20.20200
020.2020.1 202020.10000
0203 203.00000
02.0202010.10101 20202010.10101
10 rows selected