pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



Message-ID: <F10BB1FAF801D111829B0060971D839F637F94@cpsmail>
Permalink:  ../F10BB1FAF801D111829B0060971D839F637F94@cpsmail/
Also on:    postgresql.org/message-id/F10BB1FAF801D111829B0060971D839F637F94@cpsmail

 · 

reply

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