如何在 Postgresql 中删除回车符和换行符？
我再次陷入尝试以我需要的格式获取数据的困境。我有一个如下所示的文本字段。
“德安杰洛 001 德安杰洛
本地名称起源：意大利
来自美国名字 deangelo
含义：天使的
情绪谱 • 他是所有人的欢乐源泉。
个人诚信 • 他的好名声是他最宝贵的资产。 个性 • 当你被周围的人包围时，很难像雄鹰一样展翅翱翔 火鸡！关系 • 开始缓慢，但与 deangelo 随着时间的推移而建立。旅行与休闲•一次终生难忘的旅行 是他的未来。
职业生涯和金钱 • 一个有天赋的孩子，deangelo 需要 不断挑战。
生命的机会 • 快乐和幸福等待着这个有福的人。
deangelo 的幸运数字：12 • 38 • 18 • 34 • 29 • 16
"
在 Postgresql 中删除回车符和换行符的最佳方法是什么？我已经尝试了几种方法，但没有一个愿意这样做。
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;
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
local origin of name: italain
from the american name deangelo
meaning: of the angels
emotional spectrum • he is a fountain of joy for all.
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.career & money • a gifted child, deangelo will need to be
challenged constantly.life’s opportunities • joy and happiness await this blessed person.
deangelo’s lucky numbers: 12 • 38 • 18 • 34 • 29 • 16
"
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;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
阅读手册 http://www.postgresql.org/docs/current/静态/functions-matching.html
read the manual http://www.postgresql.org/docs/current/static/functions-matching.html
OP 特别询问了正则表达式，因为似乎存在对许多其他字符以及换行符的担忧，但对于那些只是想要删除换行符的人，您甚至不需要转到正则表达式。你可以简单地这样做：
我认为这是一种 SQL 标准行为，因此它应该扩展到 Postgres 的所有版本（也许是最早的版本）。以上在 9.4 和 9.2 中对我来说测试良好
OP asked specifically about regexes since it would appear there's concern for a number of other characters as well as newlines, but for those just wanting strip out newlines, you don't even need to go to a regex. You can simply do:
I think this is an SQL-standard behavior, so it should extend back to all but perhaps the very earliest versions of Postgres. The above tested fine for me in 9.4 and 9.2
我在 postgres d/b 中遇到了同样的问题，但有问题的换行符不是传统的 ascii CRLF，它是一个 unicode 行分隔符，字符 U2028。上面的代码片段也将捕获该 unicode 变体。
更新...虽然我只在“野外”遇到过上述字符，但要遵循 lmichelbacher 的建议翻译更多unicode newline-like 字符，使用这个：
I had the same problem in my postgres d/b, but the newline in question wasn't the traditional ascii CRLF, it was a unicode line separator, character U2028. The above code snippet will capture that unicode variation as well.
Update... although I've only ever encountered the aforementioned characters "in the wild", to follow lmichelbacher's advice to translate even more unicode newline-like characters, use this:
如果您需要从字符串的开头或结尾删除换行符，可以使用以下方法：
请记住，帽子
^表示字符串的开头美元符号$表示字符串结束。希望它能帮助某人。
In the case you need to remove line breaks from the begin or end of the string, you may use this:
Have in mind that the hat
^means the begin of the string and the dollar sign$means the end of the string.Hope it help someone.