postgresql替换换行符

需要导出PLM里的产品数据列表，发现不论是直接复制粘贴，还是在postgresql客户端里通过执行查询结果导入文件里。数据都跟查询出来的数量对不上，一开始考虑是不是excel单个字段容量问题。最后发现是"备注"字段有换行符。

一番搜索，通过在查询语句里

replace(replace(字段,chr(13),''),chr(10),'')As 字段

来去掉换行符就好了。

贴一下这段sql留作纪念，

select no,name,creator,ctime,state,
replace(replace(smemo,chr(13),''),chr(10),'')As smemo,
series,originno,describe,monitor,fpno,color,khmc,
khdh,dkxh,dkno,series2,suser01,voltage,speed,dyfwrd,eddyqd,eddyrd,gysdm,tsyq,suser03,
hbdj
from product where del = 0 and wkaid<>'3' order by no。

对了，上周SAP联表查询所有有效用户对应的角色及Tcode查询语句也纪念一下。

select BNAME,b.AGR_NAME,d.TEXT,c.TCODE,e.NAME_TEXTC
from
USR02 as a
inner join USER_ADDR as e on a.BNAME = e.BNAME
inner join AGR_USERS as b on e.BNAME = b.UNAME

inner join AGR_TCODES as c on b.AGR_NAME = c.AGR_NAME 
inner join AGR_TEXTS as d on c.AGR_NAME = d.AGR_NAME

where a.UFLAG = 0 
order by BNAME