Posted by Erland Sommarskog on 09/17/07 21:18

Yas (yasar1@gmail.com) writes:
> 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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]