From: | jose soares <jose(at)sferacarta(dot)com> |
---|---|
To: | Karel Zak - Zakkr <zakkr(at)zf(dot)jcu(dot)cz> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] substring extraction |
Date: | 1999-11-26 14:25:24 |
Message-ID: | 383E9854.A3E71A41@sferacarta.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)zf(dot)jcu(dot)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)
> ------------------------------------------------------------------------------
>
> ************
From | Date | Subject | |
---|---|---|---|
Next Message | Grzegorz Przeździecki | 1999-11-26 14:30:03 | Re: [HACKERS] Vacuum |
Previous Message | Zeugswetter Andreas SEV | 1999-11-26 10:29:44 | AW: AW: [HACKERS] Re: [GENERAL] drop/rename table and transaction s |