pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedanother simple SQL question
8+ messages / 7 participants
[nested] [flat]
* another simple SQL question
@ 2007-06-25 15:28 Joshua <joshua@joshuaneil.com>
2007-06-25 15:41 ` Re: another simple SQL question Nicholas Barr <nicky@chuckie.co.uk>
2007-06-25 15:44 ` Re: another simple SQL question Daniel T. Staal <DStaal@usa.net>
2007-06-25 16:02 ` Re: another simple SQL question Glenn Davy <glenn@tangelosoftware.net>
2007-06-25 16:04 ` Re: another simple SQL question Derrick Betts <list@blueaxis.com>
2007-06-25 16:40 ` Re: [SQL] another simple SQL question Jean-David Beyer <jeandavid8@verizon.net>
2007-06-25 17:10 ` Re: another simple SQL question manchicken <manchicken@notsosoft.net>
0 siblings, 6 replies; 8+ messages in thread
From: Joshua @ 2007-06-25 15:28 UTC (permalink / raw)
To: pgsql-sql; pgsql-novice@postgresql.org; pgsql-general@postgresql.org
Ok here is another simple question from a novice....
Here is what my table looks like
firstname lastname fullname
---------- ---------- -----------
smith, john
green, susan
white, jeff
How can I break the fullname field into firstname lastname fields so it
looks like the following:
firstname lastname fullname
--------- --------- ---------
john smith smith, john
susan green green, susan
jeff white white, jeff
Please let me know. Sorry for such simple novice questions, I appreciate
your support.
THANKS!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: another simple SQL question
2007-06-25 15:28 another simple SQL question Joshua <joshua@joshuaneil.com>
@ 2007-06-25 15:41 ` Nicholas Barr <nicky@chuckie.co.uk>
5 siblings, 0 replies; 8+ messages in thread
From: Nicholas Barr @ 2007-06-25 15:41 UTC (permalink / raw)
To: Joshua <joshua@joshuaneil.com>; +Cc: pgsql-sql; pgsql-novice@postgresql.org; pgsql-general@postgresql.org
> Ok here is another simple question from a novice....
>
> Here is what my table looks like
>
> firstname lastname fullname
> ---------- ---------- -----------
> smith, john
> green, susan
> white, jeff
>
>
> How can I break the fullname field into firstname lastname fields so it
> looks like the following:
>
> firstname lastname fullname
> --------- --------- ---------
> john smith smith, john
> susan green green, susan
> jeff white white, jeff
>
> Please let me know. Sorry for such simple novice questions, I appreciate
> your support.
>
> THANKS!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
temp=# create table temptable3 (firstname varchar(32), lastname
varchar(32), fullname varchar(32)) without oids;
CREATE TABLE
temp=# insert into temptable3 (fullname) values ('smith, john');
INSERT 0 1
temp=# insert into temptable3 (fullname) values ('spencer, frank');
INSERT 0 1
temp=# select * from temptable3; firstname | lastname | fullname
-----------+----------+----------------
| | smith, john
| | spencer, frank
(2 rows)
temp=# update temptable3 set firstname=trim(substring(fullname from
position(',' in fullname) + 1)), lastname=trim(substring(fullname from 0
for position(',' in fullname)));
UPDATE 2
temp=# select * from temptable3; firstname | lastname | fullname
-----------+----------+----------------
john | smith | smith, john
frank | spencer | spencer, frank
(2 rows)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: another simple SQL question
2007-06-25 15:28 another simple SQL question Joshua <joshua@joshuaneil.com>
@ 2007-06-25 15:44 ` Daniel T. Staal <DStaal@usa.net>
5 siblings, 0 replies; 8+ messages in thread
From: Daniel T. Staal @ 2007-06-25 15:44 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
On Mon, June 25, 2007 11:28 am, Joshua said:
> Ok here is another simple question from a novice....
>
> Here is what my table looks like
>
> firstname lastname fullname
> ---------- ---------- -----------
> smith, john
> green, susan
> white, jeff
>
>
> How can I break the fullname field into firstname lastname fields so it
> looks like the following:
>
> firstname lastname fullname
> --------- --------- ---------
> john smith smith, john
> susan green green, susan
> jeff white white, jeff
>
> Please let me know. Sorry for such simple novice questions, I appreciate
> your support.
The best way to handle this would actually be to break it _before_ putting
it in to the database. In fact, you can just have the first and last name
fields then and get the 'fullname' from combining the two. (Assuming you
don't care about middle names, appliations, or the anything else.)
That's just a comment: I don't know what stage you are in on this database
project.
Assuming you have the database and need to work with it my response to the
above would be to write a perl script to go through and populate the first
and last name fields. There may be an easier way though.
Daniel T. staal
---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: another simple SQL question
2007-06-25 15:28 another simple SQL question Joshua <joshua@joshuaneil.com>
@ 2007-06-25 16:02 ` Glenn Davy <glenn@tangelosoftware.net>
5 siblings, 0 replies; 8+ messages in thread
From: Glenn Davy @ 2007-06-25 16:02 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
On Tue, 26 Jun 2007 01:28:40 am Joshua wrote:
> Ok here is another simple question from a novice....
>
> Here is what my table looks like
>
> firstname lastname fullname
> ---------- ---------- -----------
> smith, john
> green, susan
> white, jeff
>
>
> How can I break the fullname field into firstname lastname fields so it
> looks like the following:
>
> firstname lastname fullname
> --------- --------- ---------
> john smith smith, john
> susan green green, susan
> jeff white white, jeff
>
> Please let me know. Sorry for such simple novice questions, I appreciate
> your support.
>
update yourtable set firstname= substring(fullname,1,position(',' in
fullname)-1) ,lastname= substring(fullname, position(', ' in fullname)+2,
char_length(fullname));
glenn
> THANKS!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: another simple SQL question
2007-06-25 15:28 another simple SQL question Joshua <joshua@joshuaneil.com>
@ 2007-06-25 16:04 ` Derrick Betts <list@blueaxis.com>
2007-06-25 16:14 ` Re: another simple SQL question Glenn Davy <glenn@tangelosoftware.net>
5 siblings, 1 reply; 8+ messages in thread
From: Derrick Betts @ 2007-06-25 16:04 UTC (permalink / raw)
To: Joshua <joshua@joshuaneil.com>; +Cc: pgsql-novice@postgresql.org
Try this:
SELECT substr(fullname, 1, position(',' IN fullname)-1) AS first,
trim(substr(fullname, position(',' IN fullname)+1, length(fullname)))
AS last
FROM table_name;
Joshua wrote:
> Ok here is another simple question from a novice....
>
> Here is what my table looks like
>
> firstname lastname fullname
> ---------- ---------- -----------
> smith, john
> green, susan
> white, jeff
>
>
> How can I break the fullname field into firstname lastname fields so it
> looks like the following:
>
> firstname lastname fullname
> --------- --------- ---------
> john smith smith, john
> susan green green, susan
> jeff white white, jeff
>
> Please let me know. Sorry for such simple novice questions, I appreciate
> your support.
>
> THANKS!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: another simple SQL question
2007-06-25 15:28 another simple SQL question Joshua <joshua@joshuaneil.com>
2007-06-25 16:04 ` Re: another simple SQL question Derrick Betts <list@blueaxis.com>
@ 2007-06-25 16:14 ` Glenn Davy <glenn@tangelosoftware.net>
0 siblings, 0 replies; 8+ messages in thread
From: Glenn Davy @ 2007-06-25 16:14 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
hey joshua
On Tue, 26 Jun 2007 02:04:20 am Derrick Betts wrote:
> Try this:
> SELECT substr(fullname, 1, position(',' IN fullname)-1) AS first,
> trim(substr(fullname, position(',' IN fullname)+1, length(fullname)))
> AS last
> FROM table_name;
>
do you understand the difference between what derrick has put here, and my
post? Derricks displays the data, mine sets it so that you can then just use
a simple select, so make sure you pick which will be useful for your
usecase. If you can use the update form to populate first and last names,
then get rid of the 'fullname' field and only populate the first and last
names from now on, that would probably also be smart. (we call this
normalising... getting rid of redundant data)
also note derricks use of TRIM to clean of the white space... thats also a
good idea i didnt use in my example
glenn
> Joshua wrote:
> > Ok here is another simple question from a novice....
> >
> > Here is what my table looks like
> >
> > firstname lastname fullname
> > ---------- ---------- -----------
> > smith, john
> > green, susan
> > white, jeff
> >
> >
> > How can I break the fullname field into firstname lastname fields so it
> > looks like the following:
> >
> > firstname lastname fullname
> > --------- --------- ---------
> > john smith smith, john
> > susan green green, susan
> > jeff white white, jeff
> >
> > Please let me know. Sorry for such simple novice questions, I appreciate
> > your support.
> >
> > THANKS!
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] another simple SQL question
2007-06-25 15:28 another simple SQL question Joshua <joshua@joshuaneil.com>
@ 2007-06-25 16:40 ` Jean-David Beyer <jeandavid8@verizon.net>
5 siblings, 0 replies; 8+ messages in thread
From: Jean-David Beyer @ 2007-06-25 16:40 UTC (permalink / raw)
To: pgsql-sql; pgsql-novice@postgresql.org; pgsql-general@postgresql.org
Joshua wrote:
> Ok here is another simple question from a novice....
>
> Here is what my table looks like
>
> firstname lastname fullname
> ---------- ---------- -----------
> smith, john
> green, susan
> white, jeff
>
>
> How can I break the fullname field into firstname lastname fields so it
> looks like the following:
>
> firstname lastname fullname
> --------- --------- ---------
> john smith smith, john
> susan green green, susan
> jeff white white, jeff
>
> Please let me know. Sorry for such simple novice questions, I appreciate
> your support.
>
How I would do it would be to write a trivial application program to do it.
I spent a long time working on databases of telephone directory information,
and we needed to look up people by name, by address, by town, etc.
It turned out that the best way to handle finding-name fields was to leave
the first, middle, and last names in one field. A big problem is
multicultural. Some people (e.g., Chinese) tend to give family name first
followed by given name. Others (e.g., English) tend to give given names
first, followed by family name. Telephone operating companies do not get
these things correct, so it better just to keep them together.
The relational database management system we used, that I originally wrote
for UNIX, allowed partial matching on fields, and I even put a SOUNDEX
scheme on the name fields.
Thus, a query like 'ristorante italiano' would locate 'Moms Pizza Italian
Restaurant' without doing a sequential search of the entire database.
Similarly, 'T S Eliot' would locate 'Eliot Thomas Stearns'.
Problems remain, such as 'Doug Mc Ilroy' would not find 'M Douglas McIlroy"
the way we built the program below.
You might look here:
http://ieeexplore.ieee.org/xpl/freeabs_all.jsp?tp=&arnumber=810466&isnumber=16537
for one way to do this. It explains briefly how to make a suitable index for it.
--
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 12:25:02 up 4 days, 20:00, 3 users, load average: 4.25, 4.14, 4.12
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: another simple SQL question
2007-06-25 15:28 another simple SQL question Joshua <joshua@joshuaneil.com>
@ 2007-06-25 17:10 ` manchicken <manchicken@notsosoft.net>
5 siblings, 0 replies; 8+ messages in thread
From: manchicken @ 2007-06-25 17:10 UTC (permalink / raw)
To: pgsql-sql
On Monday 25 June 2007 10:28:40 Joshua wrote:
> Ok here is another simple question from a novice....
>
> Here is what my table looks like
>
> firstname lastname fullname
> ---------- ---------- -----------
> smith, john
> green, susan
> white, jeff
>
>
> How can I break the fullname field into firstname lastname fields so it
> looks like the following:
>
> firstname lastname fullname
> --------- --------- ---------
> john smith smith, john
> susan green green, susan
> jeff white white, jeff
>
> Please let me know. Sorry for such simple novice questions, I appreciate
> your support.
>
> THANKS!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
You may want to consider breaking those out into individual columns. It's
much easier to put those together later on than it is to separate them out.
--
~ manchicken <><
(A)bort, (R)etry, (I)nfluence with large hammer.
09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-06-25 17:10 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-25 15:28 another simple SQL question Joshua <joshua@joshuaneil.com>
2007-06-25 15:41 ` Nicholas Barr <nicky@chuckie.co.uk>
2007-06-25 15:44 ` Daniel T. Staal <DStaal@usa.net>
2007-06-25 16:02 ` Glenn Davy <glenn@tangelosoftware.net>
2007-06-25 16:04 ` Derrick Betts <list@blueaxis.com>
2007-06-25 16:14 ` Glenn Davy <glenn@tangelosoftware.net>
2007-06-25 16:40 ` Jean-David Beyer <jeandavid8@verizon.net>
2007-06-25 17:10 ` manchicken <manchicken@notsosoft.net>
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