This is assuming that location and category are limited to 1-2 character
codes...
testing=> create table mytable ( location varchar(2), category
varchar(2), code varchar(9));
CREATE
testing=> insert into mytable (code) VALUES ('10CE4578P');
INSERT 228523 1
testing=> insert into mytable (code) VALUES ('9E457CT');
INSERT 228524 1
testing=> insert into mytable (code) VALUES ('10E457CT');
INSERT 228525 1
testing=> insert into mytable (code) VALUES ('9CE457CT');
INSERT 228526 1
testing=> update mytable set location=substr(code, 1, 1) WHERE code ~*
'^[0-9][^0-9]';
UPDATE 2
testing=> update mytable set location=substr(code, 1, 2) WHERE code ~*
'^[0-9][0-9][^0-9]';
UPDATE 2
testing=> update mytable set category=substr(code, 2, 1) WHERE code ~*
'^[0-9][^0-9][0-9]';
UPDATE 1
testing=> update mytable set category=substr(code, 2, 2) WHERE code ~*
'^[0-9][^0-9][^0-9][0-9]';
UPDATE 1
testing=> update mytable set category=substr(code, 3, 2) WHERE code ~*
'^[0-9][0-9][^0-9][^0-9][0-9]';
UPDATE 1
testing=> update mytable set category=substr(code, 3, 1) WHERE code ~*
'^[0-9][0-9][^0-9][0-9]';
UPDATE 1
testing=> select * from mytable;
location|category|code
--------+--------+---------
9|E |9E457CT
9|CE |9CE457CT
10|CE |10CE4578P
10|E |10E457CT
(4 rows)
If you are interfacing an app that is regex capable you could easily
extract the parts with '^([0-9]{1,2})([a-zA-Z]{1,2})(.....)$' to give
you the three parts (this is a perl/php'ism, not sure that the {}'s are
standard regex).
Hope this helps,
-DEJ
> -----Original Message-----
> Hi,
> I'm really foxed on this. In a PostgreSQL database table,
>
> I have three fields. "category","language","code"
>
> "code" is made up of category+language+code-numbers
> for example: 10CE4578P
> or
> 9E457CT
>
> So in this case, 10 is the 'category', CE is the 'language',
> and the rest makes
> up the code.
> or
> So in the 2nd case, 9 is the 'category', E is the 'language',
> and the rest
> makes up the code.
>
> I have 2758 records with the third field, 'code' filled in
> already. Thats how
> it comes. I want to extract the two first parts, and put them
> into their
> respective fields. That is, take the '10' and put it into the
> 'category' field,
> take the CE and put it into the 'language' field, and leave
> the 'code' field as
> it is.
>
> It looks like some sort of repetative statement needs to be
> written so it steps
> down the entire table, adjusting each record as it goes, but
> I'm at a loss as
> how to do it.
>
> If I could discover a way to simply extract this info from
> the 'code' field on
> the fly, with accuracy, I could probably forget the two fields of
> category,language and simpy search on the code field, but I
> can't work it out.
> Maybe I need to take a break...
>
>
> Does anyone have any ideas? I could really use some help on
> this one...
>
> Thanks
> Robert
