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


Yas

re: MS SQL compare columns to generate display name

On 17 Sep, 13:32, Yas <yas...@gmail.comwrote:
By the way is this even possible in MS SQL? :-)

Cheers
Yas

re: MS SQL compare columns to generate display name

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:

re: MS SQL compare columns to generate display name

Yas (yasar1@gmail.com) writes: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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

re: MS SQL compare columns to generate display name

On 17 Sep, 23:18, Erland Sommarskog <esq...@sommarskog.sewrote: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

re: MS SQL compare columns to generate display name

Yas (yasar1@gmail.com) writes: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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx