
Hi,

I need in the SELECT query extract substring 'cccc' from string 
'aaa.bbbbb.cccc.dd.eee' (extract third field from string if 
delimiter is '.').

It is easy if I know where is begin/end of 'cccc' and I can 
use the substring() function:

select substring('aaa.bbbbb.cccc.dd.eee' from 11 for 4);
substr
------
cccc

But how extract it if I don't know where is position of the second 
and third '.'? 

Yes, I know the function position() or textpos(), but this return first 
a position of the substring...

For this exist nice UN*X command "cut -f3 -d." , but how make it in 
SQL?

I ask about it, because I write for me this as new function in C, but 
I'm not sure if not exist other (better) way for it.

						Karel


------------------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz>                      http://home.zf.jcu.cz/~zakkr/

Docs:         http://docs.linux.cz                          (big docs archive)	
Kim Project:  http://home.zf.jcu.cz/~zakkr/kim/              (process manager)
FTP:          ftp://ftp2.zf.jcu.cz/users/zakkr/              (C/ncurses/PgSQL)
------------------------------------------------------------------------------


Thu, 25 Nov 1999 19:38:31 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
substring extraction
Try this:

--returns the $2 field delimited by $3
drop function field(text,int,text);
create function field(text,int,text) returns text as
'declare
        string text;
        pos int2:= 0;
        pos1 int2:= 0;
        times int2:= 0;
        totpos int2:= 0;
begin
        times:= $2 - 1;
        string:= $1;
        while totpos < times loop
                string:= substr(string,pos+1);
                pos:= strpos(string,$3);
                totpos:= totpos + 1;
        end loop;
        string:= substr(string,pos+1);
        pos1:= strpos(string,$3);
        return substr(string,1,pos1 - 1);
 end;
' language 'plpgsql';

select field('primo.secondo.terzo',1,'.');
field
-----
primo
(1 row)

select field('primo.secondo.terzo',2,'.');
field
-------
secondo
(1 row)

select field('primo.secondo.terzo',3,'.');
field
-----
terzo
(1 row)


Josï¿½



Karel Zak - Zakkr ha scritto:

> Hi,
>
> I need in the SELECT query extract substring 'cccc' from string
> 'aaa.bbbbb.cccc.dd.eee' (extract third field from string if
> delimiter is '.').
>
> It is easy if I know where is begin/end of 'cccc' and I can
> use the substring() function:
>
> select substring('aaa.bbbbb.cccc.dd.eee' from 11 for 4);
> substr
> ------
> cccc
>
> But how extract it if I don't know where is position of the second
> and third '.'?
>
> Yes, I know the function position() or textpos(), but this return first
> a position of the substring...
>
> For this exist nice UN*X command "cut -f3 -d." , but how make it in
> SQL?
>
> I ask about it, because I write for me this as new function in C, but
> I'm not sure if not exist other (better) way for it.
>
>                                                 Karel
>
> ------------------------------------------------------------------------------
> Karel Zak <zakkr@zf.jcu.cz>                      http://home.zf.jcu.cz/~zakkr/
>
> Docs:         http://docs.linux.cz                          (big docs archive)
> Kim Project:  http://home.zf.jcu.cz/~zakkr/kim/              (process manager)
> FTP:          ftp://ftp2.zf.jcu.cz/users/zakkr/              (C/ncurses/PgSQL)
> ------------------------------------------------------------------------------
>
> ************


Fri, 26 Nov 1999 15:25:24 +0100
jose soares <jose@sferacarta.com>
Re: [HACKERS] substring extraction


On Fri, 26 Nov 1999, jose soares wrote:

> Try this:
> 
> --returns the $2 field delimited by $3
> drop function field(text,int,text);
> create function field(text,int,text) returns text as
> 'declare
>         string text;
>         pos int2:= 0;
>         pos1 int2:= 0;
>         times int2:= 0;
>         totpos int2:= 0;
> begin
>         times:= $2 - 1;
>         string:= $1;
>         while totpos < times loop
>                 string:= substr(string,pos+1);
>                 pos:= strpos(string,$3);
>                 totpos:= totpos + 1;
>         end loop;
>         string:= substr(string,pos+1);
>         pos1:= strpos(string,$3);
>         return substr(string,1,pos1 - 1);
>  end;
> ' language 'plpgsql';
> 

 Oh, it is great! But my implementation in C for this is 
a little longer (only) :-) 

I send this question to the hacker list because "extract delimited 
substring" is not a abnormal uses's request, and (IMHO) will very 
good if this will in PgSQL. How much uses known write this in 
C or any PL? 

'C' implementafion "extract delimited substring":
-----------------------------------------------
text
*strcut( text *string, char *d, int field )
{
	char	*ptr 	= NULL, 
		*p 	= NULL,
		*pe 	= NULL;
	text	*result = NULL;
	int	siz;
	
	ptr  = VARDATA(string);
	*(ptr+ (VARSIZE(string) - VARHDRSZ)) = '\0';
	
	for(p = ptr; *p != '\0'; p++) {
		if (field == 1)
			break;
		if (*p == (int) d)
			--field;
	}	
	if (!*p)
		return textin("");
		
	for(pe = p; *pe != '\0'; pe++) {
		if (*pe == (int) d)
			break;
	}	
	
	result = (text *) palloc(sizeof(text) * (siz = pe - p) + VARHDRSZ);
	strncpy(VARDATA(result), p, siz);
	
	*(VARDATA(result) + siz) = '\0'; 	
	VARSIZE(result) = siz + VARHDRSZ;
	return result;
}

CREATE FUNCTION         strcut(text, char, int)
        RETURNS         text
        AS              '@module_dir@'
        LANGUAGE        'c';
  

template1=> select strcut('aaa.bbb.ccc', '.', 2);
strcut
------
bbb


						Karel


Fri, 26 Nov 1999 15:36:45 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] substring extraction
Karel Zak wrote:

> On Fri, 26 Nov 1999, jose soares wrote:
>
> > Try this:
> >
> > --returns the $2 field delimited by $3
> > drop function field(text,int,text);
> > create function field(text,int,text) returns text as
> > 'declare
> >         string text;
> >         pos int2:= 0;
> >         pos1 int2:= 0;
> >         times int2:= 0;
> >         totpos int2:= 0;
> > begin
> >         times:= $2 - 1;
> >         string:= $1;
> >         while totpos < times loop
> >                 string:= substr(string,pos+1);
> >                 pos:= strpos(string,$3);
> >                 totpos:= totpos + 1;
> >         end loop;
> >         string:= substr(string,pos+1);
> >         pos1:= strpos(string,$3);
> >         return substr(string,1,pos1 - 1);
> >  end;
> > ' language 'plpgsql';
> >
>
>  Oh, it is great! But my implementation in C for this is
> a little longer (only) :-)
>
> I send this question to the hacker list because "extract delimited
> substring" is not a abnormal uses's request, and (IMHO) will very
> good if this will in PgSQL. How much uses known write this in
> C or any PL?

    What about this one:

        create function field(text,int,text) returns text as '
            return [lindex [split $1 $3] $2]
        ' language 'pltcl';

    It  does  all  the  work  as  long as the third argument is a
    single  character.  For  multibyte  delimiters  it  will   be
    slightly  bigger,  but  not  much.  Now you might imagine why
    PL/Tcl was the first language I created.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Sat, 27 Nov 1999 00:22:12 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] substring extraction