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