Postgresql query to update fields using a regular expression
Something like...:
UPDATE tableSET 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.
If you just want to take Street_Address_1 and strip out any leading numbers, you can do this:
UPDATE tableSET 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.