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 wrote:
> 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

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 wrote:

>On 17 Sep, 13:32, Yas wrote:
>> 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

Re: MS SQL compare columns to generate display name

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: MS SQL compare columns to generate display name

On 17 Sep, 23:18, Erland Sommarskog wrote:
> Yas (yas...@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.
>

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:
> 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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx