用Oracle的TRIM函数去除字符串首尾指定字符

SELECT TRIM('abc '), ltrim('abc '), rtrim('abc ') FROM dual;

SELECT /*TRIM(';a;b;c;'),*/ ltrim(';a;b;c;',';'), rtrim(';a;b;c;',';')  FROM dual;

注意：无法使用TRIM(‘;a;b;c;’, ‘;’)的格式
但是TRIM()却有它自己的格式
SELECT TRIM(';' FROM ';a;b;c;'),
       TRIM(leading ';' FROM ';a;b;c;'),
       TRIM(trailing ';' FROM ';a;b;c;'),
       TRIM(both ';' FROM ';a;b;c;')
  FROM dual;

SELECT LTRIM('thetomsbthhe', 'the'),RTRIM('thetomsbthhe', 'the') FROM dual;

select * from
(
--租用--
select rownum as 编号,gxrxm as 承租人 ,(case trim(gxrxb ) when '1' then '男' when '2' then '女' else '不详' end ) as性别,
       rtrim( mobilephone ||','|| LXDH, ',') as 联系方式 , (select (select gxmc from gxxx where t2. gx= gxdm and rownum= 1) fromwrxxb t2
       where t1.djh=djh and rownum=1 and rq=(select min( rq) from wrxxb where t1. djh= djh)) 关系 ,rq as 办理日期 ,'租用' as业务类型
       --,mwje as 墓价,zmj 成交价,(mwje-zmj) as 优惠金额 ,(SELECT mj FROM mwdmxx WHERE djh=T1.djh) as 面积
       from ywdjb t1 where to_char(rq ,'yyyy-mm-dd')>= '2015-07-01' and to_char(rq, 'yyyy-mm-dd')<='2015-07-31'   and zxflag= '0'
union all
--续租--
select rownum as 编号,t2.gxrxm  as 承租人 ,(case trim(t2 .gxrxb ) when '1' then '男' when '2' then '女' else '不详' end ) as 性别,rtrim( t2.mobilephone|| ','||t2.LXDH,',' ) as联系方式 ,
       (select (select gxmc from gxxx where t3 .gx =gxdm and rownum=1) fromwrxxb t3
       where t1.djh=djh and rownum=1 and rq=(select min( rq) from wrxxb where t1. djh= djh)) 关系 ,t1.rq as 办理日期 ,t1 .ywmc as业务类型
       from qtywb t1 ,ywdjb t2 where t1 .djh =t2 .djh and to_char(t1. rq, 'yyyy-mm-dd')>='2015-07-01' and
       to_char(t1.rq,'yyyy-mm-dd' )<='2015-07-31'  and t1 .zxflag ='0' and  t1.ywmc= '续租'
union all
--合葬--
select rownum as 编号,t2.gxrxm  as 承租人 ,(case trim(t2 .gxrxb ) when '1' then '男' when '2' then '女' else '不详' end ) as 性别,rtrim( t2.mobilephone|| ','||t2.LXDH,',' ) as联系方式 ,
       (select (select gxmc from gxxx where t3 .gx =gxdm and rownum=1) fromwrxxb t3
       where t1.djh=djh and rownum=1 and rq=(select min( rq) from wrxxb where t1. djh= djh)) 关系 ,t1.rq  as 办理日期 ,t1 .ywmc as业务类型
       from qtywb t1 ,ywdjb t2 where t1 .djh =t2 .djh and to_char(t1. rq, 'yyyy-mm-dd')>='2015-07-01' and
       to_char(t1.rq,'yyyy-mm-dd' )<='2015-07-31'  and t1 .zxflag ='0' and  t1.ywmc= '合葬'
union all
--老墓改造--
select rownum as 编号,t2.gxrxm  as 承租人 ,(case trim(t2 .gxrxb ) when '1' then '男' when '2' then '女' else '不详' end ) as 性别,rtrim( t2.mobilephone|| ','||t2.LXDH,',' ) as联系方式 ,
       (select (select gxmc from gxxx where t3 .gx =gxdm and rownum=1) fromwrxxb t3
       where t1.djh=djh and rownum=1 and rq=(select min( rq) from wrxxb where t1. djh= djh)) 关系 ,t1.rq as 办理日期 ,t1 .ywmc as业务类型
       from qtywb t1 ,ywdjb t2 where t1 .djh =t2 .djh and to_char(t1 .rq ,'yyyy-mm-dd')>= '2015-07-01' and
       to_char(t1.rq,'yyyy-mm-dd' )<='2015-07-31'  and t1 .zxflag ='0' and t1.ywmc= '老墓改造'
)
order by 办理日期