Postgresql query to update fields using a regular expression

Question

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

123 Main Street

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.

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

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

Solution 1

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 substring form is detailed shortly after the start of the section.

Solution 2

If you just want to take Street_Address_1 and strip out any leading numbers, you can do this:

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)).

This version allows things like "1212 15th Street" to work properly.