postgresql 剔除换行、空格、回车符

---

一、项目背景

1、项目需求

业务组同事反映，使用navicat从数据库抽取数据粘贴到excel里会出现错行现象。

2、实施时间

2021-01-12

二、 技术背景

1、阅读文章所需知识背景

1）了解navicat基本使用方法
2）具有少量sql基础知识，了解replace用法

2、开发环境

1）Win10-64
2）PostgreSQL 9.6.0

3、开发工具

1）Navicat Premium 12.1

三、实施步骤

1、创建测试数据集

建表，导入少量数据用于测试

DROP TABLE IF EXISTS "public"."t_temp_result";
CREATE TABLE "public"."t_temp_result" (
  "user_name" text COLLATE "pg_catalog"."default",
  "content" text COLLATE "pg_catalog"."default",
  "title" text COLLATE "pg_catalog"."default",
  "note_time" text COLLATE "pg_catalog"."default"
)
;

INSERT INTO "public"."t_temp_result" VALUES ('kaixin1.', '入手了一件烟灰色阔腿裤
太好搭配了 我晕
黑色过于正式了 烟灰色就刚刚好啦
够长 我165能快碰地 s码的 码大更长
不会过于阔腿 刚刚好的 显瘦！
夏天也可以穿 夏天配个bm上衣 够辣了吧
一条好裤子可以穿个好几年了 值哦
快递拆开里面有个小香袋
所以裤子没有牛仔味 都可以直接穿了
店里有送运费险 大家放心入
（我已经穿去跨年了：D
记录放在最后一张
⛰️：oroi
开心一点的日常分享
身高165 94（微🍐身材
📷：🍎11原相机输出
祝大家2021更加开心！', '高腰辣妹裤 神裤果然名不虚传！', '2020-12-31 19:48:46');
INSERT INTO "public"."t_temp_result" VALUES ('kiki小鲍', '嗨 这里小鲍
又来更新跳舞穿什么系列啦‼️之前一直更新的是上衣吊带，今天来更新超级酷的设计感，小心机牛仔裤卫裤
这些裤子搭配之前推荐的上衣跳舞超级好看绝绝子
[派对R]
今天的分享就到这里啦，喜欢双击哟‼️
好物分享 好物推荐 秋冬穿搭 每日穿搭
跳舞穿什么 牛仔裤 卫裤 爵士[派对R]@穿搭薯 @薯队长 @时髦小姐姐', '跳舞穿什么❗️超酷设计感小心机牛仔裤卫裤', '2020-12-31 12:31:27');
INSERT INTO "public"."t_temp_result" VALUES ('杨奶酪_', '收到这条裤子实在是太绝了！！
那个心我刚开始以为是涂上去的
没想到是绣上去的
不过有点线头～无所谓啦还要啥自行车
衣服的长度对我来说刚刚好
和这个裤子简直就是绝配！！！
上衣：Tuku宝藏女孩
裤子：港风style（是pd多哦）', '30r的裤子到了！一套不过百真香！！！', '2021-01-02 19:02:31');
INSERT INTO "public"."t_temp_result" VALUES ('是西西吖', '今天给你们带来的是裤子合集，我选了6条裤子，都是超显瘦的，你们喜欢哪一件呢？
1⃣️molyvivi火山裤
2⃣️胖子王老板
3⃣️羊城故事
4⃣️酸奶的衣橱
5⃣️意米其
6⃣️酸奶的衣橱', '秋冬6条超显腿瘦腿直的裤子，轻松get筷子！', '2020-12-30 16:08:40');
INSERT INTO "public"."t_temp_result" VALUES ('3木', '喜欢不要忘记关🐷我哦
平价裤子#学生党#秋冬裤子#灯芯绒#街头欧美', '好绝的裤子！街头欧美风🍂秋冬平价不撞款🍂', '2021-01-04 14:11:03');
INSERT INTO "public"."t_temp_result" VALUES ('一只魚eer', '买了三家店铺，不同的加绒灰色卫裤，回来以后只留下这家灰色运动裤，真的绝了，颜色是很高级的灰色不是那种发白的灰色，内里是加厚的水貂绒，我不穿秋裤零下都一点不会冷，零下10度我穿秋裤也不冷超级棒！其他的颜色要么不对或者显胖，这个只要40元的运动裤对于我这个身高也不会短，然后膝盖我穿了好几次一点都不会鼓包，垂坠感超级好一点也不显胖！
店铺还有加长常规款小个子款，抽绳束腿款收脚款，不加绒加绒加水貂绒羊羔绒选项，满足所有人！1688买的羊毛袜几块也超级值！卫裤准备再买一条不加绒，姐妹们快给我买它！🤭🤭🤭
我搭配的🍑买的羊毛雪地靴和1688羊毛袜
外套是黑哥家机车服', '40元垂坠感超级好的灰色卫裤运动裤💕', '2021-01-02 21:16:48');
INSERT INTO "public"."t_temp_result" VALUES ('小红薯红红红', '冬天太懒了 就会想穿懒人裤
加绒卫裤就是懒人裤穿一条也不冷
油叽叽雪地靴也太暖了 光脚穿像踩着棉花暖和
穿了一个冬天的搭配 太温暖了也舒适
出门回头率超级高呀～喜欢', 'mini靴配个灰色卫裤超赞', '2021-01-02 01:01:11');
INSERT INTO "public"."t_temp_result" VALUES ('芝大七earth', '✨今日份整理了平日里爱穿的10条裤子来分享！
👉直筒裤、九分裤、阔腿裤、格纹裤、萝卜裤都有！各种身材的姐妹都有适合你的！
👉裤装分为两期！把牛仔裤单独合集一期！下期见牛仔裤分享哦！
1⃣第一条：
SUMX 九分西装裤：面料不薄不厚！适合四季穿搭！裤型显瘦显腿直。
2⃣第二条：
诗凡黎 白色九分直筒裤：面料厚实保暖！九分长度不拖沓显腿直。
3⃣第三条：
Winnie Tang 灰色萝卜裤：全澳毛可机洗面料，藏肉性很好！上身中性帅气感。
4⃣第四条：
诗凡黎 格纹阔腿裤：李沁同款，面料偏薄！适合春夏穿搭，长度是的小个子！
5⃣第五条：
范思蓝恩 黑色直筒裤：上身显瘦显腿直！长度适合小个子！面料偏薄。
6⃣第六条：
贝丽花园 针织束口裤：裤脚长度友好！束口设计能露出纤细脚踝！可运动可温柔！腰围偏大。
7⃣第七条：
非凡衣都 格纹九分直筒裤：面料厚实保暖，适合冬季穿搭！显腿直。
8⃣第八条：
贝丽花园 微喇阔腿裤：可休闲可知性的裤子，面料也比较厚实保暖适合冬季穿搭。
9⃣第九条：
WHOOO’S 黑色微喇长裤：雪尼尔面料非常有质感也很厚实！重点是上身非常显瘦显腿直！微喇设计又略带女人味。
🔟第十条：
缇吻 燕麦色九分锥形裤：九分长度加锥形裤设计也是很适合梨形身材穿着的裤型，面料教厚实！再穿一条打底裤也有松动空间！
|秋冬穿搭|每日穿搭|穿搭技巧
@穿搭薯 @时髦小姐姐 @视频薯', '10条百搭裤子合集|九分裤 直筒裤 阔腿裤✨', '2021-01-05 17:12:14');
INSERT INTO "public"."t_temp_result" VALUES ('比你nice', '我身高170体重98买的尺码是26的，我里面穿了一条加绒的打底裤，穿上刚刚好，等天气暖和了穿应该更合适一点哈～ 我觉得买一条质量好的裤子可以穿很久，很划算！
我是在🔗：zavk旗舰店 购入的喔[赞R]
我补充一下！因为我想要紧身一点的效果，所以买小了一点，按照正常码应该没有这么紧身的！不用担心显胯的问题哈集美们！而且我身高骨架也在那里，我不是骨架小的类型，这个裤子包容性很强的', '救命叻！这裤子的版型和质量真的值得入！', '2021-01-04 15:22:09');
INSERT INTO "public"."t_temp_result" VALUES ('江江酱_', '最近真的超爱这条裤子了，一直有小姐妹要链接🔗，链接放在最后两张图，价格真的没得说，质量也很🉑️，颜色是有一点灰的，是薄款，冬天穿的话一定要穿秋裤哦，也比较有弹性，我买的是s码的，里面穿一个秋裤是完全没问题的，价格真的🉑️，这个价位还要什么自行车🚲，最后！它真的超显腿瘦腿直，我是属于小腿有点外翻的，穿上之后真的很显腿直！爱了爱了，也洗过好几次，没有起球，也没有掉色', '超级显瘦的裤子👖！拼夕夕挖宝🔗', '2021-01-02 22:15:38');

2、抽取数据

SELECT
	user_name,
	content,
	title,
	note_time
FROM t_temp_result
LIMIT 10

抽取数据，粘贴excel后发现确实存在错行现象，10行数据变成了70+行。仔细观察

3、修改sql剔除换行符

仔细检查，发现数据中存在换行符（chr(10) 换行 chr(13) 回车）。excel识别了这些换行符，因此出现了错行。 这里我们可以直接使用postgresql中提供的replace方法，剔除数据中的换行符。

SELECT
	replace(replace(user_name,chr(13),''),chr(10),'') AS user_name,
	replace(replace(content,chr(13),''),chr(10),'') AS content,
	replace(replace(title,chr(13),''),chr(10),'') AS title,
	note_time
FROM t_temp_result
LIMIT 10

总结

直接复制粘贴数据的做法并不可靠，数量少的时候尚有这样那样的问题，还是推荐使用python编写脚本抽取数据直接生成excel。阅读本文的过程中如有任何问题欢迎随时与我联系。