Обсуждение: 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!

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

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