Menu
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?
786
The PostgreSQL SPLIT_PART() function is used to split a string from a specific delimiter and these queries return the nth substring. Let's analyze the above syntax: The string argument is the string to be split. The delimiter is a string used as the delimiter for splitting.
The STRING_SPLIT(string, separator) function in SQL Server splits the string in the first argument by the separator in the second argument. To split a sentence into words, specify the sentence as the first argument of the STRING_SPLIT() function and ' ' as the second argument. FROM STRING_SPLIT( 'An example sentence.
We can use any of the string to split it; we can also use a column name as a substring to split the data from the column. Delimiter argument is used to split the string into sub-parts by using a split_part function in PostgreSQL. We can split the string into a number of parts using delimiter.
The PostgreSQL split_part function is used to split a given string based on a delimiter and pick out the desired field from the string, start from the left of the string. Example: PostgreSQL SPLIT_PART() function : In the example below, the delimiter of the defined string is '-#-' and specified field number is 2.
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;
$$;
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.)
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
