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
