PostgreSQL 替换字符串右侧的字符 [英] PostgreSQL replace characters to right of string

问题描述

我需要通过删除街道名称右侧的所有内容来清理 PostgreSQL 8.4 中的地址字段.这包括删除套房(100 Broadway Street Suite 100")和更正街道名称后附有单元号的名称(100 Broadway Street100")，以便在这两种情况下的结果都是100 Broadway Street".

I need to clean up an address field in PostgreSQL 8.4 by removing everything to the right of a street name. This includes dropping suites ("100 Broadway Street Suite 100") and correcting names that have unit numbers appended to the street name ("100 Broadway Street100") so that the result in both cases would be "100 Broadway Street".

本质上，我试图删除街道"右侧的所有内容.如果不对每种情况单独编码，我似乎无法让替换功能正常工作.rtrim 函数也不起作用，因为我想删除的字符是通配符.

Essentially I am trying to remove everything to the right of "Street". I can't seem to get a replace function to work without individually coding for each case. A rtrim function also doesn't work because the characters I want removed would be a wildcard.

这是我正在努力工作的内容:

Here is what I am trying to get to work:

update *tablename* set *fieldname* = replace (*fieldname*, '%STREET%', '%STREET')

以下 SQL 有效，但我不想对每种可能的组合进行编码:

This SQL below works, but I don't want to code each possible combination:

UPDATE *tablename* set *fieldname* = replace (*fieldname*, ' SUITE 100', '');
UPDATE *tablename* set *fieldname* = replace (*fieldname*, ' STREET100', ' STREET');

如何在不明确指定Street"后面的内容的情况下删除字符串Street"右侧的所有内容?

How can I remove everything to the right of a string "Street" without explicitly specifying what follows "Street"?

感谢您的帮助.

推荐答案

试试这个:

SELECT regexp_replace('100 broadway street 100', '(.*)(Street).*', '\1\2', 'i');

上面基本上是在寻找任何跟在Street"之后的东西(不区分大小写，根据最后一个'i' agrument)，然后在Street"之后剥离所有东西，我认为这就是你所要求的.参见 http://www.postgresql.org/docs/current/static/功能-matching.html 了解更多详情.

The above is basically looking for anything followed by "Street" (case insensitively, per the last 'i' agrument), and then stripping off everything after the "Street", which I think is what you're asking for. See http://www.postgresql.org/docs/current/static/functions-matching.html for more details.

这篇关于PostgreSQL 替换字符串右侧的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！