PostgreSQL查询以使用正则表达式更新字段 [英] Postgresql query to update fields using a regular expression

问题描述

我的街道地址_1列中包含以下数据：

I have the following data in my "Street_Address_1" column:

Main Street 123

123 Main Street

使用Postgresql，我将如何编写查询以更新地址表中的 Street_Name列？换句话说， Street_Name为空白，我想使用 Street_Address_1列中包含的街道名称值进行填充。

Using Postgresql, how would I write a query to update the "Street_Name" column in my Address table? In other words, "Street_Name" is blank and I'd like to populate it with the street name value contained in the "Street_Address_1" column.

据我所知，我想使用 regexp_matches字符串方法。不幸的是，我运气不太好。

From what I can tell, I would want to use the "regexp_matches" string method. Unfortunately, I haven't had much luck.

注意：您可以假定所有地址都采用 StreetNumber StreetName StreetType格式。

NOTE: You can assume that all addresses are in a "StreetNumber StreetName StreetType" format.

推荐答案

类似...：

UPDATE table
SET Street_Name = substring(Street_Address_1 FROM '^[0-9]+ ([a-zAZ]+) ')

请参见 PGSQL 8.3.7文档的相关部分，子字符串表单在该部分开始后不久进行了详细说明。

See relevant section from PGSQL 8.3.7 docs, the substring form is detailed shortly after the start of the section.

这篇关于PostgreSQL查询以使用正则表达式更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！