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.

Matthew Wood · Accepted Answer · 2009-12-29 16:10:30Z

28

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.

answered Dec 29, 2009 at 16:10

Matthew Wood

16.5k6 gold badges49 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Martelli · Accepted Answer · 2009-06-01 20:26:42Z

25

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.

answered Jun 1, 2009 at 20:26

Alex Martelli

887k175 gold badges1.3k silver badges1.4k bronze badges

5 Comments

Huuuze Over a year ago

No luck with that regex. I'm strugglging with the regex in this case more so than the query.

Milen A. Radev Over a year ago

More like '^[0-9]+ ([a-zA-Z ]+)'

Huuuze Over a year ago

That returns the "123 Main" portion of the address. I strictly want "Main".

Huuuze Over a year ago

And only the fist occurrence of the text. I don't care if the street name has two words in it (i.e., 123 Grand Central Avenue).

Milen A. Radev Over a year ago

This then - '^[0-9]+ (.*) [a-zA-Z ]+$'

Collectives™ on Stack Overflow

Postgresql query to update fields using a regular expression

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related