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

Question

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

"DAVE EBERT CONSTRUCTION~139 LENNOX STREET~SANTA CRUZ, CA 95060~~Business Phone Number:(831) 818-3170"

I would like to do a query like:

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

But string_to_array does not "return" an array so it can't be chained in this way. However, it does return an array that can be used by other functions that take arrays like array_upper() so I don't know why it would 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]

Is there a more direct way to do this? But again, if I am extracting a lot of different array elements, maybe the less direct way performs better because you are converting string to array only once?

Answer 1

Try...

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

You just need those parenthesis.

Answer 2

Use some extra ():

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