Concatenate Lname and Fname Columns

I'm using Access 2002 and Sql 2000.
I have a Lname and Fname columns that I'm try to concatenate. I'm trying to
use Lname + ', ' + Fname in a View. But Lname is the things that displays.
What am I doing wrong?
Thanks for the help,
Paulpjscott wrote:
> I'm using Access 2002 and Sql 2000.
> I have a Lname and Fname columns that I'm try to concatenate. I'm
> trying to use Lname + ', ' + Fname in a View. But Lname is the things
> that displays.
> What am I doing wrong?
> Thanks for the help,
> Paul
Are you using fixed-length character columns? If so, you'll need to trim
the data. For example:
create table #Names (
LName1 CHAR(20),
LName2 VARCHAR(20),
FName1 CHAR(20),
FName2 VARCHAR(20) )
go
Insert Into #Names Values (
'Gugick', 'Gugick','David','David')
Insert Into #Names Values (
'Smith', 'Smith','Dan','Dan')
go
Select
LName1 + ', ' + FName1 as "Fixed-Length Name",
LName2 + ', ' + FName2 as "Variable-Length Name",
RTRIM(LName1) + ', ' + RTRIM(FName1) as "Fixed-Length Name - Trimmed"
from #Names
Go
Drop Table #Names
go
David Gugick - SQL Server MVP
Quest Software

Computed columns

I have a table with fields called fname (First Name) and lname (Last
Name). I need the users email thai is compose from lname and fname:
LOWER(LEFT (fname,1) + lname)

Is there any difference between creatig this computed column ia a table
or in a view in SQL Server 2000?

I can do:

1. CREATE TABLE Users(
fname varchar(20),
lname varchar(20),
email as LOWER(LEFT (fname,1) + lname) )

Or

2. CREATE TABLE Users (
fname varchar(20),
lname varchar(20))

CREATE VIEW Vw_users (fname, Lname ,
email)
AS
SELECT fname, Lname ,
LOWER(LEFT (fname,1) + lname) )

Is one of them is better?

Paulo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:3fa28c1c$0$201$75868355@.news.frii.net...
> I have a table with fields called fname (First Name) and lname (Last
> Name). I need the users email thai is compose from lname and fname:
> LOWER(LEFT (fname,1) + lname)
> Is there any difference between creatig this computed column ia a table
> or in a view in SQL Server 2000?
> I can do:
> 1. CREATE TABLE Users(
> fname varchar(20),
> lname varchar(20),
> email as LOWER(LEFT (fname,1) + lname) )
> Or
> 2. CREATE TABLE Users (
> fname varchar(20),
> lname varchar(20))
> CREATE VIEW Vw_users (fname, Lname ,
> email)
> AS
> SELECT fname, Lname ,
> LOWER(LEFT (fname,1) + lname) )
>
> Is one of them is better?
> Paulo
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Adding a computed column means the value is computed on demand (unless you
index that column) when you query the table. The view is rather like storing
a query definition in the database, so the value is 'calculated' only when
you query the view (unless you index the view). In that sense, both
approaches are very similar.

However, the view is a separate object, which means you can have separate
security permissions on it. A view will also be more portable to other
database systems, if that is a consideration for you.

In any case, neither solution is a good one, assuming that this isn't a
theoretical question. You will quickly have duplicate email addresses, even
with just a few names. If you have John Smith, and then James Smith is
added, their email addresses would be the same, so the best all-round
solution is to use a normal table column for the email address.

Simon