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
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 |