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

Re: string parsing

From: Joe Conway <mail(at)joeconway(dot)com>
To: Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: string parsing
Date: 2002-10-01 16:53:03
Message-ID: 3D99D2EF.4080906@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-general
Jean-Christian Imbeault wrote:
> Is there an SQl query that will parse a string on a separator (whites 
> pace) and return one word per row in the result set? (I don't know *any* 
> perl so I can't write a PL/PGSQL function, and I'm worried that a perl 
> function wouldn't be multi-byte safe ...)

In 7.2.x you could create your own C function to do this.

In 7.3beta, you could also create a PL/pgSQL function (note PL/pgSQL is not 
the same as PL/Perl). For example:

CREATE OR REPLACE FUNCTION parse_words(text)
RETURNS SETOF text AS '
   DECLARE
     i int := 0;
     word text;
   BEGIN
     LOOP
       i := i + 1;
       SELECT INTO word split_part($1, '' '', i);
       IF word = '''' THEN
         EXIT;
       END IF;
       RETURN NEXT word;
     END LOOP;
     RETURN;
   END
' LANGUAGE 'plpgsql';

select * from parse_words('abc def hij klm');
  parse_words
-------------
  abc
  def
  hij
  klm
(4 rows)

This should be multi-byte safe.

HTH,

Joe


In response to

pgsql-general by date

Next:From: Tom LaneDate: 2002-10-01 17:13:45
Subject: Re: Query plan not using index for some reason.
Previous:From: Stephan SzaboDate: 2002-10-01 16:25:39
Subject: Re: Query plan not using index for some reason.

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