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:

	IN in_text TEXT,
	IN in_delimiter VARCHAR(20)
-- 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 (
-- Modified By: Chris West
    elems text[];      
    elems := string_to_array(in_text, in_delimiter);
    FOR i IN array_lower(elems, 1) .. array_upper(elems, 1) LOOP
      RETURN NEXT elems[i];
$$ 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;
Categories: BlogSQL

1 Comment

Alva Malovich · August 11, 2011 at 1:43 AM

Thanks a lot for providing individuals with such a wonderful chance to check tips from this web site. It is always very excellent and as well , jam-packed with amusement for me and my office acquaintances to search your web site at least three times a week to read the new tips you have. And definitely, I am also certainly pleased for the incredible tips you serve. Certain 4 facts in this posting are rather the very best we have had.

Leave a Reply

Your email address will not be published. Required fields are marked *