How to return one element from string_to_array () in PostgreSQL 8.4?

Question

I want to parse a field with the following value type:

"CONSTRUCTION DEVA EBERT ~ 139 LENNOX STREET ~ SANTA CRUZ, CA 95060 ~~ Work phone number: [phone]"

I would like to make a request like:

Update mytable set street = string_to_array(myfield,'~')[2]

But string_to_array does not "return" the array, so it cannot be bound in this way. However, it returns an array that can be used by other functions that accept arrays of type array_upper (), so I don't know why this will not work.

My workaround is to create an array field and do this:

Update mytable set myfield_array = string_to_array(myfield,'~')
Update mytable set street = myfield_array[2]

? , , , , ?

Answer 1

Try...

Update mytable set street = (string_to_array(myfield,'~'))[2]

.

Answer 2

():

Update mytable set street = (string_to_array(myfield,'~'))[2]