14 rows selected SQL> select substr(job, 1, 5) from emp where length(job)<=5 2 union all 3 select substr(job, 1, 4) from emp where length(job)>5;
SUBSTR(JOB,1,5) --------------- CLERK CLERK CLERK CLERK SALE SALE MANA SALE MANA MANA ANAL PRES SALE ANAL
14 rows selected
select decode(sign(length(title)-30),-1,title,substr(title,1,28)||'...') from yourTable;
有些问题,当title为空的时候,多了"..."
select decode(nvl(sign(length(title)-30),-1),-1,title,substr(title,1,28)||'...') from yourTable;
select case when length(a) <= 30 then a else substr(a, 1, 28) || '...' end a from ( select 'abcdefghijklmnopqrstuvwxyz28--aaaaaaadd' a from dual union all select 'abcdefghijklmnopqrstuvwxyz2830' a from dual union all select 'abcd' a from dual ) t
select decode(title,length(title)<30,title,substr(title,1,28)||'...') from table
这个问题最终是用case when去表达比较清晰.
select decode(sign(length(title)-30),-1,title,substr(title,1,28)||'...') as title from Table;
from yourTable;
SQL> select job from emp;
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
14 rows selected
SQL> select substr(job, 1, 5) from emp where length(job)<=5
2 union all
3 select substr(job, 1, 4) from emp where length(job)>5;
SUBSTR(JOB,1,5)
---------------
CLERK
CLERK
CLERK
CLERK
SALE
SALE
MANA
SALE
MANA
MANA
ANAL
PRES
SALE
ANAL
14 rows selected
select decode(sign(length(title)-30),-1,title,substr(title,1,28)||'...')
from yourTable;
from yourTable;
else substr(a, 1, 28) || '...'
end a
from
(
select 'abcdefghijklmnopqrstuvwxyz28--aaaaaaadd' a
from dual
union all
select 'abcdefghijklmnopqrstuvwxyz2830' a
from dual
union all
select 'abcd' a
from dual
) t
decode(title,length(title)<30,title,substr(title,1,28)||'...')
from table
from Table;
from yourTable;
这个语句是没有问题的。
建议试下。