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