Name storage in database

I'm dealing with a system that only stores a single "Name" field in an OLTP
(web site) database. Now my immediate reaction was to split it into: Prefix,
First, Middle, Last & Suffix. However, the UI could easilly get a little
unfriendly. So I popped into Amazon and found they were using a single field
in the UI too. Other sites I checked only used first and last names.
Someone must have done some research on this and made a paper available.
Thoughts and/or links?On Wed, 5 Dec 2007 12:37:24 -0800, Jay wrote:
>I'm dealing with a system that only stores a single "Name" field in an OLTP
>(web site) database. Now my immediate reaction was to split it into: Prefix,
>First, Middle, Last & Suffix. However, the UI could easilly get a little
>unfriendly. So I popped into Amazon and found they were using a single field
>in the UI too. Other sites I checked only used first and last names.
>Someone must have done some research on this and made a paper available.
>Thoughts and/or links?
>
Hi Jay,
It all depends on what you want to use the data for.
If you just intend to print it on address labels for your business
correspondence, than you can just as well leave it in a single column.
If you intend to do something wiith a component of a name (e.g. sorting
by last name, or personalizing a letter by having it start with "Dear
Mr. Jones" or "Hi Barry", or analysing whether your customers with a
specific title spend more than those without), then you're better off
splitting the parts you eed seperate right from the off.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Name storage in database

I'm dealing with a system that only stores a single "Name" field in an OLTP
(web site) database. Now my immediate reaction was to split it into: Prefix,
First, Middle, Last & Suffix. However, the UI could easilly get a little
unfriendly. So I popped into Amazon and found they were using a single field
in the UI too. Other sites I checked only used first and last names.
Someone must have done some research on this and made a paper available.
Thoughts and/or links?On Wed, 5 Dec 2007 12:37:24 -0800, Jay wrote:

>I'm dealing with a system that only stores a single "Name" field in an OLTP
>(web site) database. Now my immediate reaction was to split it into: Prefix
,
>First, Middle, Last & Suffix. However, the UI could easilly get a little
>unfriendly. So I popped into Amazon and found they were using a single fiel
d
>in the UI too. Other sites I checked only used first and last names.
>Someone must have done some research on this and made a paper available.
>Thoughts and/or links?
>
Hi Jay,
It all depends on what you want to use the data for.
If you just intend to print it on address labels for your business
correspondence, than you can just as well leave it in a single column.
If you intend to do something wiith a component of a name (e.g. sorting
by last name, or personalizing a letter by having it start with "Dear
Mr. Jones" or "Hi Barry", or analysing whether your customers with a
specific title spend more than those without), then you're better off
splitting the parts you eed seperate right from the off.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

mystery truncating

I have table in SQL Server 2000 that stores comments in one field of type
varchar with a length of 250. A test value entered is 249 characters long.
When I run the following:
select len(comment) from tblcomments where emplid = '241s'
the len returned is 249, as expected. However, once I try concatenating
date and user name info, funny stuff starts happening.
For example, if I run this code:
select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
Comment)
from tblcomments where emplid = '241S'
len returned is 284, which is correct. But when the code is run without
using the len() function the result returned has been truncated by 28
characters. I end up with a character output of only 256 characters. I get
the date, the ntusername and the first 221 characters of the comment sting.
For some reason, after concatenating the additional info I'm losing 28
characters and the output is limited to 256 characters. But again, when
using the len function I'm getting 284. What is happening? Any suggestions
?> characters. I end up with a character output of only 256 characters
Where, in QA?
Go to Tools - Options... - Results and increase "Maximum characters per
column". Max value allowed is 8K or 8192.
AMB
"kiloez" wrote:

> I have table in SQL Server 2000 that stores comments in one field of type
> varchar with a length of 250. A test value entered is 249 characters long
.
> When I run the following:
> select len(comment) from tblcomments where emplid = '241s'
> the len returned is 249, as expected. However, once I try concatenating
> date and user name info, funny stuff starts happening.
> For example, if I run this code:
> select len(Cast([Date] as varchar(128)) + ' - ' + NTUSERNAME + ' - ' +
> Comment)
> from tblcomments where emplid = '241S'
> len returned is 284, which is correct. But when the code is run without
> using the len() function the result returned has been truncated by 28
> characters. I end up with a character output of only 256 characters. I g
et
> the date, the ntusername and the first 221 characters of the comment sting
.
> For some reason, after concatenating the additional info I'm losing 28
> characters and the output is limited to 256 characters. But again, when
> using the len function I'm getting 284. What is happening? Any suggestio
ns?
>|||Alejandro, thank you. How can this be done when running the same code from
a
stored procedure? I don't see a similar option in Enterprise Mgr. The
problem was first noticed when running this code from a sp.
"Alejandro Mesa" wrote:
> Where, in QA?
> Go to Tools - Options... - Results and increase "Maximum characters per
> column". Max value allowed is 8K or 8192.
>
> AMB
> "kiloez" wrote:
>|||Where did you run the sp?
This is an internal setting of the client app (QA).
AMB
"kiloez" wrote:
> Alejandro, thank you. How can this be done when running the same code fro
m a
> stored procedure? I don't see a similar option in Enterprise Mgr. The
> problem was first noticed when running this code from a sp.
> "Alejandro Mesa" wrote:
>|||Alejandro, thanks again for replying. I found out what the problem was. I'
m
actually running this sp from a dataenvironment in VB6. After checking the
properties for the sp in the DE I saw that the length for the output
parameter was only 250. After changing it to 8000 output was as it should
be. The character length had already been changed in the sp itself.
"Alejandro Mesa" wrote:
> Where did you run the sp?
> This is an internal setting of the client app (QA).
>
> AMB
> "kiloez" wrote:
>