如何在Postgresql中删除回车符和换行符？ [英] How to remove carriage returns and new lines in Postgresql?

问题描述

全部

我再次陷入尝试以我需要的格式获取数据的情况。我有一个文本字段，如下所示。

I am stuck again trying to get my data in a format that I need it in. I have a text field that looks like this.

deangelo 001 deangelo

"deangelo 001 deangelo

名称的本地来历：italain

local origin of name: italain

从美国名字deangelo

from the american name deangelo

含义：天使中的

情感谱•他是所有人欢乐的源泉。

emotional spectrum • he is a fountain of joy for all.

人格正直•他的好名声是他最宝贵的资产。
人格•很难与之匹敌老鹰，当您被
火鸡包围时•关系开始缓慢，但与
deangelo的关系会随着时间而建立旅途与休闲•一生中
的旅行将在他的未来。

personal integrity • his good name is his most precious asset. personality • it’s hard to soar with eagles when you’re surrounded by turkeys! relationships • starts slowly, but a relationship with deangelo builds over time. travel & leisure • a trip of a lifetime is in his future.

职业和金钱•天才的孩子，deangelo将需要不断受到
的挑战。

career & money • a gifted child, deangelo will need to be challenged constantly.

生活的机会•快乐和幸福正在等待这个有福的人。

life’s opportunities • joy and happiness await this blessed person.

deangelo的生活ucky数字：12•38•18•34•29•16

deangelo’s lucky numbers: 12 • 38 • 18 • 34 • 29 • 16

在Postgresql中删除回车符和换行的最佳方法是什么？我已经尝试了好几件事，但是没有一个想要表现。

What would the best way be in Postgresql to remove the carriage returns and new lines? I've tried several things and none of them want to behave.

select regexp_replace(field, E'\r\c', '  ', 'g') from mytable
    WHERE id = 5520805582

SELECT regexp_replace(field, E'[^\(\)\&\/,;\*\:.\>\<[:space:]a-zA-Z0-9-]', ' ')
    FROM mytable
    WHERE field~ E'[^\(\)\&\/,;\*\:.\<\>[:space:]a-zA-Z0-9-]'
    AND id = 5520805582;

在此先感谢
亚当

Thanks in advance, Adam

推荐答案

select regexp_replace(field, E'[\\n\\r]+', ' ', 'g' )

阅读手册 http://www.postgresql.org/docs/current/static/functions-matching.html

这篇关于如何在Postgresql中删除回车符和换行符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！