使用正则表达式更新字段的 Postgresql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/936513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql query to update fields using a regular expression
提问by Huuuze
I have the following data in my "Street_Address_1" column:
我的“Street_Address_1”列中有以下数据：
123 Main Street
大街123号
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.
使用 Postgresql，我将如何编写查询来更新地址表中的“Street_Name”列？换句话说，“Street_Name”为空，我想用“Street_Address_1”列中包含的街道名称值填充它。
From what I can tell, I would want to use the "regexp_matches" string method. Unfortunately, I haven't had much luck.
据我所知，我想使用“regexp_matches”字符串方法。不幸的是，我没有多少运气。
NOTE: You can assume that all addresses are in a "StreetNumber StreetName StreetType" format.
注意：您可以假设所有地址都采用“StreetNumber StreetName StreetType”格式。
回答by Alex Martelli
Something like...:
就像是...：
UPDATE table
SET Street_Name = substring(Street_Address_1 FROM '^[0-9]+ ([a-zAZ]+) ')
See relevant section from PGSQL 8.3.7 docs, the substringform is detailed shortly after the start of the section.
请参阅PGSQL 8.3.7 文档中的相关部分substring，该部分开始后不久将详细介绍该表单。
回答by Matthew Wood
If you just want to take Street_Address_1 and strip out any leading numbers, you can do this:
如果您只想获取 Street_Address_1 并去掉任何前导数字，您可以这样做：
UPDATE table
SET street_name = regexp_replace(street_address_1, '^[0-9]* ','','');
This takes the value in street_address_1 and replaces any leading string of numbers (plus a single space) with an empty string (the fourth parameter is for optional regex flags like "g" (global) and "i" (case-insensitive)).
这采用 street_address_1 中的值，并用空字符串替换任何前导数字字符串（加上一个空格）（第四个参数用于可选的正则表达式标志，如“g”（全局）和“i”（不区分大小写））。
This version allows things like "1212 15th Street" to work properly.
这个版本允许像“1212 15th Street”这样的东西正常工作。
