SQL sql按空格将字符串拆分为postgresql中的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1986491/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql split string by space into table in postgresql
提问by veilig
I looking for a function like regexp_split_to_table, but our db is version 8.2.9, so it doesn't have it. I'm really only splitting on a space, so a string like
我正在寻找像 regexp_split_to_table 这样的函数，但我们的数据库是 8.2.9 版，所以它没有。我真的只是在一个空格上分裂，所以像这样的字符串
how now brown cow
现在怎么棕色的牛
would return
会回来
+------+
|Column|
+------+
|how |
|now |
|brown |
|cow |
+------+
is there a simple function that can handle this, or something I have to write myself?
有没有一个简单的函数可以处理这个问题，或者我必须自己写一些东西？
回答by alvherre
You can split an array to a resultset by using the unnest function, and you can turn a string literal into an array by using the string_to_array function. Combine both and you get this:
您可以使用 unnest 函数将数组拆分为结果集，也可以使用 string_to_array 函数将字符串文字转换为数组。结合两者，你会得到这个：
alvherre=# select unnest(string_to_array('the quick lazy fox', ' '));
unnest
--------
the
quick
lazy
fox
(4 filas)
Since 8.2 does not have UNNEST, you can write it in PostgreSQL like this:
由于 8.2 没有 UNNEST，你可以在 PostgreSQL 中这样写：
create or replace function unnest(anyarray) returns setof anyelement
language sql as $$
select [i] from generate_series(array_lower(, 1),
array_upper(, 1)) as i;
$$;
回答by pilcrow
I think you'll have to RETURNS SETor RETURNS TABLEyourself.
我认为你必须RETURNS SET或RETURNS TABLE你自己。
Updated answer:using PL/pgSQL:
更新答案：使用 PL/pgSQL：
pg=> CREATE OR REPLACE FUNCTION string_to_rows(text) RETURNS SETOF TEXT AS $$
DECLARE
elems text[];
BEGIN
elems := string_to_array(, ' ');
FOR i IN array_lower(elems, 1) .. array_upper(elems, 1) LOOP
RETURN NEXT elems[i];
END LOOP;
RETURN;
END
$$ LANGUAGE 'plpgsql';
CREATE FUNCTION
pg=> SELECT "Column" FROM string_to_rows('how now brown cow') d("Column");
Column
--------
how
now
brown
cow
(4 rows)
Original answer:using PL/perl:
原始答案：使用 PL/perl：
pg=> CREATE LANGUAGE plperl;
CREATE LANGUAGE
pg=> CREATE FUNCTION psplit_to_rows(text) RETURNS SETOF TEXT AS $$
pg$> for my $t (split ' ', $_[0]) { return_next $t; }
pg$> undef;
pg$> $$ LANGUAGE plperl;
CREATE FUNCTION
pg=> SELECT "Column" FROM psplit_to_rows('how now brown cow') d("Column");
Column
--------
how
now
brown
cow
(4 rows)
Obviously you can extend this to handle a delimiter of your choosing, etc. (Note, I'm not sure if you really wanted that column named "Column", requiring identifier quoting to avoid keyword clash, but, there you are.)
显然，您可以扩展它以处理您选择的分隔符等。（注意，我不确定您是否真的想要名为“Column”的列，需要标识符引用以避免关键字冲突，但是，您就是这样。）
