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

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

MS SQL 6.5 Procedure to Send Query results via Email

Hi,
I'm not sure if this is possible as i've googled everywhere, but i have a
select query that returns a customer record with their associated sales
orders. I would like to automate a process which sends an email reminder to
each customer in the database, that has outstanding orders. This email
reminder should have the results of the query regarding their account.
The table structure are as follows.
Customer_tbl
CustomerID
AccountNo
Name
EmailAddress
Order_tbl
OrderID
CustomerID
Reference
Amount
Date
Outstanding_flg
Can anyone help?
Sen.
Hi
As discussed the below steps were performed:-
You can configure a SQL Mail account to do this and your SQl Server and SQL
Executive service
should be started using a domain account which have previlages to the mail
server.
Also you should have a mail profile configured in your SQl Server machine.
After that
you can use the Extended procedure XPS_ATRTMAIL to start mail session and
xp_sendmail
to fire a Select statement and send the result as a mal to receiver(s).
Below sample will send the output of sysobjects to
xp_sendmail @.recipients = 'hari_prasad_k@.hotmail.com',
x@.query = 'select * from sysobjects',
@.subject = 'SQL Server Report',
@.message = 'The contents of sysobjects:',
@.attach_results = 'TRUE', @.width = 250
Please have a look into books online (SQL 6.5) for below procedures to
configure mail.
xp_startmail
xp_sendmail
sp_processmail
xp_readmail
xp_deletemail
xp_stopmail
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"serendipity" <abc@.hotmail.com> wrote in message
news:40e12aae$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> --
> Customer_tbl
> --
> CustomerID
> AccountNo
> Name
> EmailAddress
> --
> Order_tbl
> --
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.
>
|||Hi
As discussed the below steps were performed:-
You can configure a SQL Mail account to do this and your SQl Server and SQL
Executive service
should be started using a domain account which have previlages to the mail
server.
Also you should have a mail profile configured in your SQl Server machine.
After that
you can use the Extended procedure XPS_ATRTMAIL to start mail session and
xp_sendmail
to fire a Select statement and send the result as a mal to receiver(s).
Below sample will send the output of sysobjects to
xp_sendmail @.recipients = 'hari_prasad_k@.hotmail.com',
x@.query = 'select * from sysobjects',
@.subject = 'SQL Server Report',
@.message = 'The contents of sysobjects:',
@.attach_results = 'TRUE', @.width = 250
Please have a look into books online (SQL 6.5) for below procedures to
configure mail.
xp_startmail
xp_sendmail
sp_processmail
xp_readmail
xp_deletemail
xp_stopmail
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"serendipity" <abc@.hotmail.com> wrote in message
news:40e12aae$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> --
> Customer_tbl
> --
> CustomerID
> AccountNo
> Name
> EmailAddress
> --
> Order_tbl
> --
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.
>

MS SQL 6.5 Procedure to Send Query results via Email

Hi,

I'm not sure if this is possible as i've googled everywhere, but i have a
select query that returns a customer record with their associated sales
orders. I would like to automate a process which sends an email reminder to
each customer in the database, that has outstanding orders. This email
reminder should have the results of the query regarding their account.

The table structure are as follows.

-------
Customer_tbl
-------
CustomerID
AccountNo
Name
EmailAddress

-------
Order_tbl
-------
OrderID
CustomerID
Reference
Amount
Date
Outstanding_flg

Can anyone help?
Sen.Hi

Posting DDL (Create table statements etc) and example data (as insert
statements) will remove any ambiguity when people try to answer your
questions.

To send individual emails you will need to use a cursor to send separate
emails to each customer. Check out xp_sendmail in Books online for details
on how to send emails from SQL Server.

You will have to be careful when configuring SQL Mail on SQLServer 6.5 and
the version of outlook you use.

INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231

HTH

John

"serendipity" <abc@.hotmail.com> wrote in message
news:40e12993$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> -------
> Customer_tbl
> -------
> CustomerID
> AccountNo
> Name
> EmailAddress
> -------
> Order_tbl
> -------
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.|||Sen,

If you have a machine with the .NET Framework that can connect to the
SQL Server, give Gadami a try
(http://www.kripsoft.com/pages/2/index.htm). Insert your database
connection information and query in an XML file (see examples shipped
with product), and run GadamiConfig.exe to send the emails out. It
has a 'burst' feature that will automatically send each set of orders
to each customer. Merely tell the program what column in the query
contains your email addresses and it will split the data and send out
emails with HTML tables of the data.

The product is free to use for sending out up to 100 emails at a time,
and has a nominal cost if you want to send out more.

-Krip

MS SQL 6.5 Procedure to Send Query results via Email

Hi,
I'm not sure if this is possible as i've googled everywhere, but i have a
select query that returns a customer record with their associated sales
orders. I would like to automate a process which sends an email reminder to
each customer in the database, that has outstanding orders. This email
reminder should have the results of the query regarding their account.
The table structure are as follows.
--
Customer_tbl
--
CustomerID
AccountNo
Name
EmailAddress
--
Order_tbl
--
OrderID
CustomerID
Reference
Amount
Date
Outstanding_flg
Can anyone help?
Sen.Hi
As discussed the below steps were performed:-
You can configure a SQL Mail account to do this and your SQl Server and SQL
Executive service
should be started using a domain account which have previlages to the mail
server.
Also you should have a mail profile configured in your SQl Server machine.
After that
you can use the Extended procedure XPS_ATRTMAIL to start mail session and
xp_sendmail
to fire a Select statement and send the result as a mal to receiver(s).
Below sample will send the output of sysobjects to
xp_sendmail @.recipients = 'hari_prasad_k@.hotmail.com',
x@.query = 'select * from sysobjects',
@.subject = 'SQL Server Report',
@.message = 'The contents of sysobjects:',
@.attach_results = 'TRUE', @.width = 250
Please have a look into books online (SQL 6.5) for below procedures to
configure mail.
xp_startmail
xp_sendmail
sp_processmail
xp_readmail
xp_deletemail
xp_stopmail
Thanks
Hari
MCDBA
--
Thanks
Hari
MCDBA
"serendipity" <abc@.hotmail.com> wrote in message
news:40e12aae$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> --
> Customer_tbl
> --
> CustomerID
> AccountNo
> Name
> EmailAddress
> --
> Order_tbl
> --
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.
>

MS SQL 6.5 Procedure to Send Query results via Email

Hi,
I'm not sure if this is possible as i've googled everywhere, but i have a
select query that returns a customer record with their associated sales
orders. I would like to automate a process which sends an email reminder to
each customer in the database, that has outstanding orders. This email
reminder should have the results of the query regarding their account.
The table structure are as follows.
Customer_tbl
--
CustomerID
AccountNo
Name
EmailAddress
Order_tbl
--
OrderID
CustomerID
Reference
Amount
Date
Outstanding_flg
Can anyone help?
Sen.Hi
As discussed the below steps were performed:-
You can configure a SQL Mail account to do this and your SQl Server and SQL
Executive service
should be started using a domain account which have previlages to the mail
server.
Also you should have a mail profile configured in your SQl Server machine.
After that
you can use the Extended procedure XPS_ATRTMAIL to start mail session and
xp_sendmail
to fire a Select statement and send the result as a mal to receiver(s).
Below sample will send the output of sysobjects to
xp_sendmail @.recipients = 'hari_prasad_k@.hotmail.com',
x@.query = 'select * from sysobjects',
@.subject = 'SQL Server Report',
@.message = 'The contents of sysobjects:',
@.attach_results = 'TRUE', @.width = 250
Please have a look into books online (SQL 6.5) for below procedures to
configure mail.
xp_startmail
xp_sendmail
sp_processmail
xp_readmail
xp_deletemail
xp_stopmail
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"serendipity" <abc@.hotmail.com> wrote in message
news:40e12aae$1@.news.syd.ip.net.au...
> Hi,
> I'm not sure if this is possible as i've googled everywhere, but i have a
> select query that returns a customer record with their associated sales
> orders. I would like to automate a process which sends an email reminder
to
> each customer in the database, that has outstanding orders. This email
> reminder should have the results of the query regarding their account.
> The table structure are as follows.
> --
> Customer_tbl
> --
> CustomerID
> AccountNo
> Name
> EmailAddress
> --
> Order_tbl
> --
> OrderID
> CustomerID
> Reference
> Amount
> Date
> Outstanding_flg
>
> Can anyone help?
> Sen.
>