From: Jean-David Beyer <jeandavid8@verizon.net>
To: pgsql-sql@postgresql.org
To: pgsql-novice@postgresql.org
To: pgsql-general@postgresql.org
Subject: Re: [SQL] another simple SQL question
Date: Mon, 25 Jun 2007 12:40:42 -0400
Message-ID: <467FF00A.8040107@verizon.net> (raw)
In-Reply-To: <467FDF28.8000108@joshuaneil.com>
References: <467FDF28.8000108@joshuaneil.com>
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeandavid8@verizon.net, pgsql-novice@postgresql.org, pgsql-general@postgresql.org
Subject: Re: [SQL] another simple SQL question
In-Reply-To: <467FF00A.8040107@verizon.net>
* 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