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?

rfusca · Accepted Answer · 2010-08-05 19:07:15Z

22

Try...

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

You just need those parenthesis.

edited Aug 5, 2010 at 19:07

answered Aug 5, 2010 at 18:44

rfusca

7,7752 gold badges32 silver badges34 bronze badges

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

1 Comment

Dave Rincon Over a year ago

Why start in 1?

Frank Heikens · Accepted Answer · 2010-08-05 18:47:42Z

4

Use some extra ():

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

answered Aug 5, 2010 at 18:47

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related