如何在PostgreSQL 8.4中从string_to_array（）返回一个元素？ [英] How do I return one element from string_to_array() in PostgreSQL 8.4?

问题描述

我想解析具有以下类型的值的字段：

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

DAVE EBERT CONSTRUCTION〜139 LENNOX STREET〜SANTA CRUZ，CA 95060 ~~公司电话号码：[phone]

"DAVE EBERT CONSTRUCTION~139 LENNOX STREET~SANTA CRUZ, CA 95060~~Business Phone Number:[phone]"

我想执行以下查询：

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

但是string_to_array不会返回数组，因此无法以这种方式链接。但是，它确实返回了一个数组，该数组可以由其他采用array_upper（）的数组的函数使用，所以我不知道为什么它不起作用。

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?

推荐答案

尝试...

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

您只需要这些括号即可。

You just need those parenthesis.

这篇关于如何在PostgreSQL 8.4中从string_to_array（）返回一个元素？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！