举例 呵呵ps: 不知道create table b ("char" varchar2(32));行不行
呵呵,你这个就不是char作为字段名了~~~
SQL> create table test(c_id varchar2(20));表已创建。SQL> insert into test values ('1234');已创建 1 行。SQL> insert into test values ('45678');已创建 1 行。SQL> insert into test values ('-1234');已创建 1 行。SQL> insert into test values ('0.1234');已创建 1 行。SQL> insert into test values ('-0.1234');已创建 1 行。SQL> insert into test values ('abc');已创建 1 行。SQL> insert into test values ('a9c');已创建 1 行。SQL> insert into test values ('-a9c');已创建 1 行。SQL> insert into test values ('-.a9c');已创建 1 行。SQL> insert into test values ('-.');已创建 1 行。SQL> insert into test values ('-');已创建 1 行。SQL> insert into test values ('.');已创建 1 行。SQL> commit;提交完成。SQL> select * from test;C_ID ---------------------------------------- 1234 45678 -1234 0.1234 -0.1234 abc a9c -a9c -.a9c -. -C_ID ---------------------------------------- .已选择12行。SQL> select * from test 2 where length(translate(c_id,'-.0123456789'||c_id,'-.0123456789'))=length(c_id) 3 and c_id not in ('-','-.','.') ;C_ID ---------------------------------------- 1234 45678 -1234 0.1234 -0.1234SQL> select * from test 2 where length(translate(c_id,'-.0123456789'||c_id,'-.0123456789'))<>length(c_id) 3 and c_id not in ('-','-.','.') 4 ;C_ID ---------------------------------------- a9c -a9c -.a9cSQL>
实现类似功能的另外一种方法就是采用ORACLE 10G的正则表达式select * from test where not REGEXP_LIKE(C_ID,'^[:punct:]|[0-9]+$')
select * from a, b where a.num = b.char;为例 这里一定是b.char里面包含了非数字的字符 查询b中相关记录即可 select * from b where translate(b.char,'#12345678','#') is not null
SQL> select * from test where not REGEXP_LIKE(C_ID,'^[:punct:]|[0-9]+$');C_ID ---------------------------------------- abc a9c -a9c -.a9c -. - .已选择7行。SQL>
提供一点测试用例create table test(num varchar2(32)); --right insert into test values('.01'); insert into test values('0.01'); insert into test values('01'); insert into test values('-01'); insert into test values('-0.01'); insert into test values('-.01'); insert into test values('+01'); insert into test values('+0.01'); insert into test values('+.01'); insert into test values('+1'); insert into test values('1'); insert into test values('-1');--wrong insert into test values('--.01'); insert into test values('ab'); insert into test values('0.ab'); insert into test values('0abc'); insert into test values('..01'); insert into test values('1..01'); insert into test values('300.0a1'); insert into test values('+-'); insert into test values('+'); insert into test values('-'); insert into test values('.'); insert into test values('['); insert into test values('*'); insert into test values('1+1'); insert into test values('1,111');
补充多几条测试用例 create table test(num varchar2(32)); --right insert into test values('.01'); insert into test values('0.01'); insert into test values('01'); insert into test values('-01'); insert into test values('-0.01'); insert into test values('-.01'); insert into test values('+01'); insert into test values('+0.01'); insert into test values('+.01'); insert into test values('+1'); insert into test values('1'); insert into test values('-1'); insert into test values(' 1'); insert into test values(' 1 '); insert into test values('1 '); insert into test values('1 '); --wrong insert into test values('--.01'); insert into test values('ab'); insert into test values('0.ab'); insert into test values('0abc'); insert into test values('..01'); insert into test values('1..01'); insert into test values('300.0a1'); insert into test values('+-'); insert into test values('+'); insert into test values('-'); insert into test values('.'); insert into test values('['); insert into test values('*'); insert into test values('1+1'); insert into test values('1,111'); insert into test values('1 1');--我写了一个正则,基本对了,但还有些问题 select * from (select num from test where REGEXP_LIKE(num, ' *^[+-]?[0-9]*[.]?[0-9]+ *$')) a where 1 = a.num;
肯定是B中的某一行的char字段是不能转化成数字造成的,用下面的语句找出所有不能转化为数字的所有的记录: select * from B where trim(translate(char,'0123456789',' ')) is not null;
select to_number( regexp_substr(txt,'^[-]?[[:digit:]]*\.?[[:digit:]]*$') ) the_number, txt from yourtable;
oracle中,如何用char作为字段名?请指教。
SQL> create table test(c_id varchar2(20));表已创建。SQL> insert into test values ('1234');已创建 1 行。SQL> insert into test values ('45678');已创建 1 行。SQL> insert into test values ('-1234');已创建 1 行。SQL> insert into test values ('0.1234');已创建 1 行。SQL> insert into test values ('-0.1234');已创建 1 行。SQL> insert into test values ('abc');已创建 1 行。SQL> insert into test values ('a9c');已创建 1 行。SQL> insert into test values ('-a9c');已创建 1 行。SQL> insert into test values ('-.a9c');已创建 1 行。SQL> insert into test values ('-.');已创建 1 行。SQL> insert into test values ('-');已创建 1 行。SQL> insert into test values ('.');已创建 1 行。SQL> commit;提交完成。SQL> select * from test;C_ID
----------------------------------------
1234
45678
-1234
0.1234
-0.1234
abc
a9c
-a9c
-.a9c
-.
-C_ID
----------------------------------------
.已选择12行。SQL> select * from test
2 where length(translate(c_id,'-.0123456789'||c_id,'-.0123456789'))=length(c_id)
3 and c_id not in ('-','-.','.') ;C_ID
----------------------------------------
1234
45678
-1234
0.1234
-0.1234SQL> select * from test
2 where length(translate(c_id,'-.0123456789'||c_id,'-.0123456789'))<>length(c_id)
3 and c_id not in ('-','-.','.')
4 ;C_ID
----------------------------------------
a9c
-a9c
-.a9cSQL>
where not REGEXP_LIKE(C_ID,'^[:punct:]|[0-9]+$')
这里一定是b.char里面包含了非数字的字符
查询b中相关记录即可
select * from b
where translate(b.char,'#12345678','#') is not null
----------------------------------------
abc
a9c
-a9c
-.a9c
-.
-
.已选择7行。SQL>
--right
insert into test values('.01');
insert into test values('0.01');
insert into test values('01');
insert into test values('-01');
insert into test values('-0.01');
insert into test values('-.01');
insert into test values('+01');
insert into test values('+0.01');
insert into test values('+.01');
insert into test values('+1');
insert into test values('1');
insert into test values('-1');--wrong
insert into test values('--.01');
insert into test values('ab');
insert into test values('0.ab');
insert into test values('0abc');
insert into test values('..01');
insert into test values('1..01');
insert into test values('300.0a1');
insert into test values('+-');
insert into test values('+');
insert into test values('-');
insert into test values('.');
insert into test values('[');
insert into test values('*');
insert into test values('1+1');
insert into test values('1,111');
create table test(num varchar2(32));
--right
insert into test values('.01');
insert into test values('0.01');
insert into test values('01');
insert into test values('-01');
insert into test values('-0.01');
insert into test values('-.01');
insert into test values('+01');
insert into test values('+0.01');
insert into test values('+.01');
insert into test values('+1');
insert into test values('1');
insert into test values('-1');
insert into test values(' 1');
insert into test values(' 1 ');
insert into test values('1 ');
insert into test values('1 ');
--wrong
insert into test values('--.01');
insert into test values('ab');
insert into test values('0.ab');
insert into test values('0abc');
insert into test values('..01');
insert into test values('1..01');
insert into test values('300.0a1');
insert into test values('+-');
insert into test values('+');
insert into test values('-');
insert into test values('.');
insert into test values('[');
insert into test values('*');
insert into test values('1+1');
insert into test values('1,111');
insert into test values('1 1');--我写了一个正则,基本对了,但还有些问题
select * from (select num from test where REGEXP_LIKE(num, ' *^[+-]?[0-9]*[.]?[0-9]+ *$')) a where 1 = a.num;
select * from B where trim(translate(char,'0123456789',' ')) is not null;