Re: [SQL] another simple SQL question

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