PostgreSQL – String To Rows

Working Solution – Custom Function

I have to say that using PostgreSQL has been great compared to MySQL. Recently, though, I ran into an issue while working with iReports designer. I needed to be able to accept a string of comma-delimited integers and then JOIN on those ID’s. Therefore, I started looking for a good string splitting function. At first I was unable to find a native one so I altered this plpgsql function made by pilcrow and added some documentation:

CREATE OR REPLACE FUNCTION split_to_rows(
	IN in_text TEXT,
	IN in_delimiter VARCHAR(20)
)
RETURNS SETOF TEXT AS $$
---------------------------------------------------------------------------
-- Function Name: split_to_rows
-- In-coming Params:
--   in_text [TEXT]
--   in_delimiter [VARCHAR(20)]
-- Out going Param:
--   TEXT
-- Description:
--   Splits the specified text with the specified delimiter and returns a
--   set of rows.
-- Created On: 2011-05-24
-- Author: pilcrow (http://stackoverflow.com/questions/1986491#1987113)
-- Modified By: Chris West
---------------------------------------------------------------------------
  DECLARE
    elems text[];      
  BEGIN
    elems := string_to_array(in_text, in_delimiter);
    FOR i IN array_lower(elems, 1) .. array_upper(elems, 1) LOOP
      RETURN NEXT elems[i];
    END LOOP;
    RETURN;
  END
$$ LANGUAGE 'plpgsql';

The following is an example of using this new function to convert a string of numbers into rows of numbers as integers.

SELECT CAST(split_to_rows('1,2,3,4,5,6', ',') AS INTEGER) AS id;

The Better Solution – Native PostgreSQL Function

After looking into other native solutions, I finally found the regexp_split_to_table function. This is basically called in the same way as pilcrow’s, but with one major difference: you can use regular expressions to split the string into rows. Here is an example of using this new function in the same was as I did the split_to_rows function:

SELECT CAST(regexp_split_to_table('1,2,3,4,5,6', ',') AS INTEGER) AS id;