sql split string by space into table in postgresql
Solution 1
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:
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:
create or replace function unnest(anyarray) returns setof anyelement
language sql as $$
select $1[i] from generate_series(array_lower($1, 1),
array_upper($1, 1)) as i;
$$;
Solution 2
I think you'll have to RETURNS SET
or RETURNS TABLE
yourself.
Updated answer: using PL/pgSQL:
pg=> CREATE OR REPLACE FUNCTION string_to_rows(text) RETURNS SETOF TEXT AS $$
DECLARE
elems text[];
BEGIN
elems := string_to_array($1, ' ');
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:
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.)
veilig
Code Monkey __ w c(..)o ( \__(-) __) /\ ( /(_)___) w /| | \ m m
Updated on January 03, 2020Comments
-
veilig over 4 years
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
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?