javier garcia - CEBAS wrote:
> 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:
Your example and the rest of the problem desciption are not consistent,
but I'm guessing something like this is at least close:
create table landuses(cod_grass int, code text);
insert into landuses values(1539,'(NJ/LI)+NJ{10:}+LI{10:}');
insert into landuses values(1847,'(AG/L)+AL{10:}');
insert into landuses values(2767,'(OL/AL)+L{20:}');
insert into landuses values(19,'LI+NJ');
insert into landuses values(20,'I');
insert into landuses values(29,'NJ');
insert into landuses values(106,'PH{:LZ40}');
insert into landuses values(111,'AG^');
insert into landuses values(112,'PD');
insert into landuses values(187,'L+AL');
insert into landuses values(189,'M');
insert into landuses values(195,'MD');
insert into landuses values(196,'L+AL{40:}');
regression=# SELECT cod_grass, code,
split_part(translate(ltrim(code,'('),'{}+/^():','\t\t\t\t\t\t\t'),'\t',1)
AS code_fixed FROM landuses;
cod_grass | code | code_fixed
-----------+-------------------------+------------
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
189 | M | M
195 | MD | MD
196 | L+AL{40:} | L
(13 rows)
Also, you didn't mention a version -- this will work on 7.3.x but not
7.2.x or before (split_part is new in 7.3).
HTH,
Joe
