MS SQL compare columns to generate display name

Hello, I have the following table with 4 columns...

firstname, lastname1, lastname2, EMAIL

Table has user names and email, I would like to generate a 5th column
called DisplayName.
The email Id is sometimes firstname.lastname1.lastname2@. and others
just firstname.lastname1@.

I would like to generate the display name exactly like the email eg
firstname.lastname1.lastname2@. displayName = firstname lastname1
lastname2.....so for james.smith display name = James Smith and for
james.earl.smith displayName = James Earl Smith etc etc

Is there a way that I can check/compare email Id (before the @. part)
with firstname, lastname1 and lastname2 and generate a display name
based on what was used for the email address?

I hope I've explained this well :-)

Many thanks in advance for any help/advise

YasOn 17 Sep, 13:32, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hello, I have the following table with 4 columns...
>
firstname, lastname1, lastname2, EMAIL
>
Table has user names and email, I would like to generate a 5th column
called DisplayName.
The email Id is sometimes firstname.lastname1.lastname2@. and others
just firstname.lastname1@.
>
I would like to generate the display name exactly like the email eg
firstname.lastname1.lastname2@. displayName = firstname lastname1
lastname2.....so for james.smith display name = James Smith and for
james.earl.smith displayName = James Earl Smith etc etc
>
Is there a way that I can check/compare email Id (before the @. part)
with firstname, lastname1 and lastname2 and generate a display name
based on what was used for the email address?

By the way is this even possible in MS SQL? :-)

Cheers
Yas|||Something is probably possible. Transact-SQL has very basic string
manipulation capability, and the CASE expression allows resolving to
different values depending on testable conditions. If you posted
CREATE TABLE and INSERTs for a variety of test data, along with
expected output, you might get a more specific response.

How confident are you that the email name matches the name in the
three name columns?

Roy Harvey
Beacon Falls, CT

On Mon, 17 Sep 2007 12:53:15 -0700, Yas <yasar1@.gmail.comwrote:

Quote:

Originally Posted by

>On 17 Sep, 13:32, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

>Hello, I have the following table with 4 columns...
>>
>firstname, lastname1, lastname2, EMAIL
>>
>Table has user names and email, I would like to generate a 5th column
>called DisplayName.
>The email Id is sometimes firstname.lastname1.lastname2@. and others
>just firstname.lastname1@.
>>
>I would like to generate the display name exactly like the email eg
>firstname.lastname1.lastname2@. displayName = firstname lastname1
>lastname2.....so for james.smith display name = James Smith and for
>james.earl.smith displayName = James Earl Smith etc etc
>>
>Is there a way that I can check/compare email Id (before the @. part)
>with firstname, lastname1 and lastname2 and generate a display name
>based on what was used for the email address?

>
>
>By the way is this even possible in MS SQL? :-)
>
>Cheers
>Yas

|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

firstname, lastname1, lastname2, EMAIL
>
Table has user names and email, I would like to generate a 5th column
called DisplayName.
The email Id is sometimes firstname.lastname1.lastname2@. and others
just firstname.lastname1@.
>
I would like to generate the display name exactly like the email eg
firstname.lastname1.lastname2@. displayName = firstname lastname1
lastname2.....so for james.smith display name = James Smith and for
james.earl.smith displayName = James Earl Smith etc etc
>
Is there a way that I can check/compare email Id (before the @. part)
with firstname, lastname1 and lastname2 and generate a display name
based on what was used for the email address?
>
I hope I've explained this well :-)

UPDATE tbl
SET DisplayName = CASE substring(lower(email),
1, charindex('@.', email) - 1)
WHEN lower(firstname) + '.' + lower(lastname)
THEN firstname + ' ' + lastname
WHEN lower(firstname) + '.' + lower(lastname) +
'.' + lower(lastname2)
THEN firstname + ' ' + lastname + ' '
lastname2
END
WHERE DisplayName IS NULL

I have here assumed that firstname, lastname and lastname2 are entered
with proper case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 17 Sep, 23:18, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Yas (yas...@.gmail.com) writes:

Quote:

Originally Posted by

firstname, lastname1, lastname2, EMAIL

>

Quote:

Originally Posted by

Table has user names and email, I would like to generate a 5th column
called DisplayName.
The email Id is sometimes firstname.lastname1.lastname2@. and others
just firstname.lastname1@.

>

Quote:

Originally Posted by

I would like to generate the display name exactly like the email eg
firstname.lastname1.lastname2@. displayName = firstname lastname1
lastname2.....so for james.smith display name = James Smith and for
james.earl.smith displayName = James Earl Smith etc etc

>

Quote:

Originally Posted by

Is there a way that I can check/compare email Id (before the @. part)
with firstname, lastname1 and lastname2 and generate a display name
based on what was used for the email address?

>

Quote:

Originally Posted by

I hope I've explained this well :-)

>
UPDATE tbl
SET DisplayName = CASE substring(lower(email),
1, charindex('@.', email) - 1)
WHEN lower(firstname) + '.' + lower(lastname)
THEN firstname + ' ' + lastname
WHEN lower(firstname) + '.' + lower(lastname) +
'.' + lower(lastname2)
THEN firstname + ' ' + lastname + ' '
lastname2
END
WHERE DisplayName IS NULL
>
I have here assumed that firstname, lastname and lastname2 are entered
with proper case.
>

Thanks! :-)
Anyone know why I'm getting the following error when I run the above?
"Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation
conflict for equal to operation."

Its all from the same Table so strange that there would be a Collation
conflict?

Thanks|||Yas (yasar1@.gmail.com) writes:

Quote:

Originally Posted by

Anyone know why I'm getting the following error when I run the above?
"Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation
conflict for equal to operation."
>
Its all from the same Table so strange that there would be a Collation
conflict?

Collation is set by column, so it could happen. Use sp_help to review the
collations.

A possible reason that you created the table, changed the database
collation, and then added more columns to the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx