name 基价:60 基价:310-折扣:1.00000000 基价:6620 基价:120-折扣:1.00000000怎么才可以只取“基价:”后,“-折扣” 前,这部分数据?SELECT substr(name,INSTR(name,1,'基价')+2,instr(name,1,'-折扣')-INSTR(name,1,'基价')) from 表
这样下面部分的数据就得不到了。 name 基价:60 基价:6620
再加一个UNION ALL 对这部分数据进来单独读取union allselect substr(name ,instr(name,1,'基价')+2,100) from biao where 没有折扣的数据
SELECT substr(name,INSTR(name,1,'基价')+2,instr(name,1,'-折扣')-INSTR(name,1,'基价')) from 表 union all select name from 表 where INSTR(name,1,'折扣') = 0
eygle@SZTYORA> create table t(name varchar2(100));表已创建。eygle@SZTYORA> insert into t(name) values('基价:60');已创建 1 行。eygle@SZTYORA> insert into t(name) values('基价:310-折扣:1.00000000');已创建 1 行。eygle@SZTYORA> insert into t(name) values('基价:6620');已创建 1 行。eygle@SZTYORA> insert into t(name) values('基价:120-折扣:1.00000000');已创建 1 行。eygle@SZTYORA> commit;提交完成。eygle@SZTYORA> eygle@SZTYORA> col name for a25; eygle@SZTYORA> col sub_name for a25; eygle@SZTYORA> select name, 2 substr(name, instr(name,'基价:')+length('基价:'), 3 decode(instr(name,'-折扣'),0,length(name),instr(name,'-折扣')-instr(name,'基价:')-length('基价:'))) as sub_name 4 from t;NAME SUB_NAME ------------------------- ------------------------- 基价:60 60 基价:310-折扣:1.00000000 310 基价:6620 6620 基价:120-折扣:1.00000000 120
drop table t purge; create table t(name varchar2(100)); insert into t(name) values('基价:60'); insert into t(name) values('基价:310-折扣:1.00000000'); insert into t(name) values('基价:6620'); insert into t(name) values('基价:120-折扣:1.00000000'); commit;col name for a25; col sub_name for a25; select name, substr(name, instr(name,'基价:')+length('基价:'), decode(instr(name,'-折扣'),0,length(name),instr(name,'-折扣')-instr(name,'基价:')-length('基价:'))) as sub_name from t;
drop table t purge; create table t(name varchar2(100)); insert into t(name) values('基价:60'); insert into t(name) values('基价:310-折扣:1.00000000'); insert into t(name) values('基价:6620'); insert into t(name) values('基价:120-折扣:1.00000000'); commit;-- 要不这样: col name for a25; col sub_name for a25; select name, substr(name, instr(name,'基价:')+length('基价:'), decode(instr(name,'-折扣'),0,length(name),instr(name,'-折扣')-instr(name,'基价:')-length('基价:'))) as sub_name from t; -- 或者这样: col name for a25; col sub_name for a25; select name, substr(name, instr(name,'基价:')+length('基价:'), instr(name||'-折扣','-折扣')-instr(name,'基价:')-length('基价:') ) as sub_name from t;
如果使用的数据库为10G或以上,还可以使用以下的SQL语句: select regexp_substr(name,'^$','[0-9]+',1,1) from table_name;
select name,substr(name,instr(name,'基价:')+length('基价:'),decode(instr(name,'折扣:'),0,length(name)-length('基价:'),instr(name,'-折扣:')-length('基价:')-instr(name,'基价:'))) from t;
我的是oracle10.2.0.3.0的版本,执行该语句出错:Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as ccpph1
SQL> select regexp_substr(name,'^$','[0-9]+',1,1) from t;
select regexp_substr(name,'^$','[0-9]+',1,1) from t
ORA-01722: 无效数字
LZ看这个,这个可以!!!!!!!!!!!!select substr( name, instr(name,':')+1, decode(instr(name,'-zhekou'),0,100,instr(name,'-zhekou'))-instr(name,':')-1 ) from test2;
select regexp_substr(name,'^$','[0-9]+',1,1) from table_name;应改为: select regexp_substr(name,'[0-9]+',1,1) from table_name;感谢各位提醒
基价:60
基价:310-折扣:1.00000000
基价:6620
基价:120-折扣:1.00000000怎么才可以只取“基价:”后,“-折扣” 前,这部分数据?SELECT substr(name,INSTR(name,1,'基价')+2,instr(name,1,'-折扣')-INSTR(name,1,'基价')) from 表
这样下面部分的数据就得不到了。
name
基价:60
基价:6620
再加一个UNION ALL 对这部分数据进来单独读取union allselect substr(name ,instr(name,1,'基价')+2,100) from biao where 没有折扣的数据
union all
select name from 表 where INSTR(name,1,'折扣') = 0
eygle@SZTYORA> col name for a25;
eygle@SZTYORA> col sub_name for a25;
eygle@SZTYORA> select name,
2 substr(name, instr(name,'基价:')+length('基价:'),
3 decode(instr(name,'-折扣'),0,length(name),instr(name,'-折扣')-instr(name,'基价:')-length('基价:'))) as sub_name
4 from t;NAME SUB_NAME
------------------------- -------------------------
基价:60 60
基价:310-折扣:1.00000000 310
基价:6620 6620
基价:120-折扣:1.00000000 120
create table t(name varchar2(100));
insert into t(name) values('基价:60');
insert into t(name) values('基价:310-折扣:1.00000000');
insert into t(name) values('基价:6620');
insert into t(name) values('基价:120-折扣:1.00000000');
commit;col name for a25;
col sub_name for a25;
select name,
substr(name, instr(name,'基价:')+length('基价:'),
decode(instr(name,'-折扣'),0,length(name),instr(name,'-折扣')-instr(name,'基价:')-length('基价:'))) as sub_name
from t;
create table t(name varchar2(100));
insert into t(name) values('基价:60');
insert into t(name) values('基价:310-折扣:1.00000000');
insert into t(name) values('基价:6620');
insert into t(name) values('基价:120-折扣:1.00000000');
commit;-- 要不这样:
col name for a25;
col sub_name for a25;
select name,
substr(name, instr(name,'基价:')+length('基价:'),
decode(instr(name,'-折扣'),0,length(name),instr(name,'-折扣')-instr(name,'基价:')-length('基价:'))) as sub_name
from t;
-- 或者这样:
col name for a25;
col sub_name for a25;
select name,
substr(name, instr(name,'基价:')+length('基价:'),
instr(name||'-折扣','-折扣')-instr(name,'基价:')-length('基价:') ) as sub_name
from t;
select regexp_substr(name,'^$','[0-9]+',1,1) from table_name;
select name,substr(name,instr(name,'基价:')+length('基价:'),decode(instr(name,'折扣:'),0,length(name)-length('基价:'),instr(name,'-折扣:')-length('基价:')-instr(name,'基价:'))) from t;
Connected as ccpph1
SQL> select regexp_substr(name,'^$','[0-9]+',1,1) from t;
select regexp_substr(name,'^$','[0-9]+',1,1) from t
ORA-01722: 无效数字
name,
instr(name,':')+1,
decode(instr(name,'-zhekou'),0,100,instr(name,'-zhekou'))-instr(name,':')-1
)
from test2;
select regexp_substr(name,'[0-9]+',1,1) from table_name;感谢各位提醒