select case when length(f)>32 then substr(f,1,32)||'...' else f end as f from( select '1234567890123456789012345678901234567890' as f from dual union select '1234' from dual)
create view v_view as select substr('最大缴费年度最大缴费年度最大缴费年度最大缴费年度最大缴费年度最大缴费年度' ,0,33)||'...' as b from dual
select a, substr(a,0,32)||'...' as b from table
按楼主的需求用scott的dept表做了个例子: SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 60 测 test 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS WUHANSQL> select dept.*, case when length(dept.loc) > 5 then substr(dept.loc, 1, 5)||'..' else substr(dep t.loc, 1, 5) end as loc1 from dept; DEPTNO DNAME LOC LOC1 ---------- -------------- ------------- ------------ 60 测 test test 10 ACCOUNTING NEW YORK NEW Y.. 20 RESEARCH DALLAS DALLA.. 30 SALES CHICAGO CHICA.. 40 OPERATIONS WUHAN WUHANSQL> create or replace view v1 as select dept.*, case when length(dept.loc) > 5 then substr(dept.loc , 1, 5)||'..' else substr(dept.loc, 1, 5) end as loc1 from dept;View created.SQL> select * from v1; DEPTNO DNAME LOC LOC1 ---------- -------------- ------------- ------------ 60 测 test test 10 ACCOUNTING NEW YORK NEW Y.. 20 RESEARCH DALLAS DALLA.. 30 SALES CHICAGO CHICA.. 40 OPERATIONS WUHAN WUHANSQL>
create or replace view vgen_webpage as select decode(t.fpagetype, '1', '静态', '2', '动态' )FpagetypeText, decode(t.FStatus, '*', '注销', '0', '有效' ) FStatusText,t.*,t.ftargetpagefname as f, case when length(f) > 32 then substr (f, 1, 32)||'..' else substr(f, 1, 32) end as ftargetpagesname from tgen_webpage t系统说我没有定义F
create or replace view vgen_webpage as select decode(t.fpagetype, '1', '静态', '2', '动态' )FpagetypeText, decode(t.FStatus, '*', '注销', '0', '有效' ) FStatusText,t.*,t.ftargetpagefname as f, case when length(t.ftargetpagefname) > 32 then substr (t.ftargetpagefname, 1, 32)||'..' else substr(t.ftargetpagefname, 1, 32) end as ftargetpagesname from tgen_webpage t
from(
select '1234567890123456789012345678901234567890' as f from dual
union
select '1234' from dual)
select substr('最大缴费年度最大缴费年度最大缴费年度最大缴费年度最大缴费年度最大缴费年度' ,0,33)||'...' as b from dual
SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
60 测 test
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS WUHANSQL> select dept.*, case when length(dept.loc) > 5 then substr(dept.loc, 1, 5)||'..' else substr(dep
t.loc, 1, 5) end as loc1 from dept; DEPTNO DNAME LOC LOC1
---------- -------------- ------------- ------------
60 测 test test
10 ACCOUNTING NEW YORK NEW Y..
20 RESEARCH DALLAS DALLA..
30 SALES CHICAGO CHICA..
40 OPERATIONS WUHAN WUHANSQL> create or replace view v1 as select dept.*, case when length(dept.loc) > 5 then substr(dept.loc
, 1, 5)||'..' else substr(dept.loc, 1, 5) end as loc1 from dept;View created.SQL> select * from v1; DEPTNO DNAME LOC LOC1
---------- -------------- ------------- ------------
60 测 test test
10 ACCOUNTING NEW YORK NEW Y..
20 RESEARCH DALLAS DALLA..
30 SALES CHICAGO CHICA..
40 OPERATIONS WUHAN WUHANSQL>
select
decode(t.fpagetype,
'1',
'静态',
'2',
'动态'
)FpagetypeText,
decode(t.FStatus,
'*',
'注销',
'0',
'有效'
) FStatusText,t.*,t.ftargetpagefname as f,
case when length(f) > 32 then substr
(f, 1, 32)||'..' else substr(f, 1, 32) end as ftargetpagesname
from tgen_webpage t系统说我没有定义F
select
decode(t.fpagetype,
'1',
'静态',
'2',
'动态'
)FpagetypeText,
decode(t.FStatus,
'*',
'注销',
'0',
'有效'
) FStatusText,t.*,t.ftargetpagefname as f,
case when length(t.ftargetpagefname) > 32 then substr
(t.ftargetpagefname, 1, 32)||'..' else substr(t.ftargetpagefname, 1, 32) end as ftargetpagesname
from tgen_webpage t