RE: [GENERAL] Dashed if I can work this out. Help needed copying substring to another field

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