sql split string by space into table in postgresql

Question

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?

regexp_split_to_table was introduced in 8.3, so you were just shy of having the feature :- ( — Randall
– Randall, Commented Dec 5, 2022 at 21:13

alvherre · Accepted Answer · 2010-01-02 22:13:54Z

52

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;
$$;

answered Jan 2, 2010 at 22:13

alvherre

2,60921 silver badges22 bronze badges

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

Comments

pilcrow · Accepted Answer · 2010-01-03 03:24:39Z

3

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.)

edited Jan 3, 2010 at 3:24

answered Dec 31, 2009 at 21:04

pilcrow

59.2k14 gold badges101 silver badges147 bronze badges

3 Comments

veilig Over a year ago

I don't have plperl to work with, can this easily be done w/ plpgsql?

veilig Over a year ago

@pilcrow, Your function works great, but I'm having a problem when I try to execute a query like [select string_to_rows(column) from table]. Keeps giving me the error: ERROR: set-valued function called in context that cannot accept a set. Looking around online I see I need to call like [select * from string_to_rows(text)]. Is it possible to call this function and pass in a set? ie [select * from string_to_rows(select col from table)].

pilcrow Over a year ago

@veilig, you're trying to put a set or table into a SQL scalar context. A separate question, showing us what data you're starting with and what result set you want, is, I think, appropriate.

Collectives™ on Stack Overflow

sql split string by space into table in postgresql

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related