current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 2 down vote favorite
1

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.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

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.

share|improve this answer
    
No luck with that regex. I'm strugglging with the regex in this case more so than the query. –  Huuuze Jun 1 '09 at 20:33
    
More like '^[0-9]+ ([a-zA-Z ]+)' –  Milen A. Radev Jun 1 '09 at 20:46
    
That returns the "123 Main" portion of the address. I strictly want "Main". –  Huuuze Jun 1 '09 at 20:50
    
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). –  Huuuze Jun 1 '09 at 20:52
    
This then - '^[0-9]+ (.*) [a-zA-Z ]+$' –  Milen A. Radev Jun 1 '09 at 21:08
up vote 7 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.