trimming functions.

From: javier garcia - CEBAS <rn001(at)cebas(dot)csic(dot)es>
To: pgsql-general(at)postgresql(dot)org
Subject: trimming functions.
Date: 2003-06-23 10:41:19
Message-ID: 200306231027.h5NARUI01870@natura.cebas.csic.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all.
I've got a table with a field called "code". This field is a code of
asociated crops and vegetation in one area. As this code is too complex for
our purposes. I need to trim the text to the main crop in every row.
Sometimes the first character is a '('. So I need to remove this first '('
and extract the first code. This first code is formed by alphabet character
and can be up to 3 haracters in length.
So, at the moment I've done:

SELECT cod_grass,code,substring(ltrim(code,'(') FROM 1 FOR 3) AS
code_trimmed FROM landuses WHERE code LIKE '(%';

An extract of the result is:

cod_grass | code | code_trimmed
-----------+-----+-------------------------+----------------
1539 | (NJ/LI)+NJ{10:}+LI{10:} | NJ/
1847 | (AG/L)+AL{10:} | AG/
2767 | (OL/AL)+L{20:} | OL/
19 | LI+NJ | LI+
20 | I | I
29 | NJ | NJ
106 | PH{:LZ40} | PH{
111 | AG^ | AG^
112 | PD | PD
187 | L+AL | L+A
189 | M | M
195 | 1 | MD | MD
196 | 2 | L+AL{40:} | L+A
...

So I still need to improve the SELECT to remove all possible symbols after
the first group of alphabet characters to get a 'code_trimmed' column with
just the characters:(NJ, AG, OL, LI, I, NJ, PH, AG, PD, L, M, MD, L).
Possible 'non alphabet' symbols are '{+/^('
Any idea?

Thanks and regards,
Javier

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2003-06-23 10:45:48 Re: PL/PGSQL -- How To Return a Temp Table
Previous Message Harry Yau 2003-06-23 10:31:41 PL/PGSQL -- How To Return a Temp Table