Skip site navigation (1) Skip section navigation (2)

Re: comma separated value splitting

From: Joe Conway <mail(at)joeconway(dot)com>
To: matlads(at)dsmagic(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: comma separated value splitting
Date: 2004-04-26 17:31:26
Message-ID: 408D476E.2070209@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-novice
Martin Atukunda wrote:
> how do i make postgres split for me the email addresses and return me a table 
> so that for 'test' I get:
> 
> name | email
> ---------------------
> test | test1(at)test(dot)com
> test | test2(at)test(dot)com
> test | test3(at)test(dot)com

You didn't mention your Postgres version. If it's 7.4.x, this will work:

create table filters (name varchar(64), filter text);
insert into filters values ('test', 'test1(at)test(dot)com, test2(at)test(dot)com, 
test3(at)test(dot)com');

CREATE TYPE filters_type AS (name varchar(64), email text);
CREATE OR REPLACE FUNCTION filters_list()
RETURNS SETOF filters_type AS '
  DECLARE
    rec record;
    retrec filters_type;
    low int;
    high int;
  BEGIN
    FOR rec IN SELECT name, string_to_array(filter,'','') AS 
filter_array
    FROM filters LOOP
      low := array_lower(rec.filter_array, 1);
      high := array_upper(rec.filter_array, 1);
      FOR i IN low..high LOOP
        retrec.name := rec.name;
        retrec.email := btrim(rec.filter_array[i]);
        RETURN NEXT retrec;
      END LOOP;
     END LOOP;
     RETURN;
   END;
' LANGUAGE 'plpgsql';

regression=# SELECT name, email FROM filters_list();
  name |     email
------+----------------
  test | test1(at)test(dot)com
  test | test2(at)test(dot)com
  test | test3(at)test(dot)com
(3 rows)

HTH,

Joe


In response to

pgsql-novice by date

Next:From: Stephan SzaboDate: 2004-04-26 18:58:55
Subject: Re: access information_schema
Previous:From: Christopher A. GoodfellowDate: 2004-04-26 16:48:43
Subject: Field with character varying (255)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group