another simple SQL question

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)

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.
---------------------------------------------------------------

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

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:

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.

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

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