From: Jackson, DeJuan <djackson@cpsgroup.com>
To: robert@chalmers.com.au, psql-general <pgsql-general@postgreSQL.org>
To: sql@chalmers.com.au
Cc: PGSQL SQL <pgsql-sql@postgreSQL.org>
Subject: RE: [GENERAL] Dashed if I can work this out. Help needed copying substring to another field
Date: Tue, 2 Feb 1999 12:17:45 -0600
Message-ID: <F10BB1FAF801D111829B0060971D839F637F94@cpsmail> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: djackson@cpsgroup.com, pgsql-general@postgreSQL.org, sql@chalmers.com.au
Subject: Re: [GENERAL] Dashed if I can work this out. Help needed copying substring to another field
In-Reply-To: <F10BB1FAF801D111829B0060971D839F637F94@cpsmail>
References: <F10BB1FAF801D111829B0060971D839F637F94@cpsmail>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox