用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;

 去掉首尾多个字符

对于去除首尾多个字符的时候，需要特别注意的是，Oracle数据库会从字符串中扫描，移除掉去除集合中出现的每一个字符，直到遇到第一个不在去除集合中的字符为止，而不是去去掉去除集合中的字符串。

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

注意：TRIM(leading 'the' FROM 'thetomsbthhe')是不行的，此函数的截取集仅能包含一个字符。

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) from wrxxb 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) from wrxxb 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) from wrxxb 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) from wrxxb 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 办理日期