April 19, 2010 at 8:07 pm
Hello All,
I have a people table with email column, for each company i need to get the generic email format keeping in mind to exclude personal emails.
For example company Freehills has email format as firstname.lastname@freehills.com. So all the contacts under that company will have this email format for example John Smith, Email - john.smith@freehills.com
For each company if the contact has an email then i need to derive the format of the email.
I would appreciate if anyone could show me some direction.
Your suggestions are higly appreciated.
Thanks in advance,
Shilpa.
April 19, 2010 at 10:55 pm
well, you could infer it is fname.lname by counting the number of periods left of the @ symbol:
declare @email varchar(1000)
set @email = 'firstname.lastname@freehills.com'
select SUBSTRING(@email,1,CHARINDEX('@',@email)),
REPLACE(SUBSTRING(@email,1,CHARINDEX('@',@email)),'.',''),
LEN(SUBSTRING(@email,1,CHARINDEX('@',@email))) - len(REPLACE(SUBSTRING(@email,1,CHARINDEX('@',@email)),'.',''))
firstname.lastname@ firstnamelastname@1
if the last val is one or more, you can kinda assume it's that format, right?
Lowell
April 20, 2010 at 7:52 pm
hi Lowell, Bhuvnesh,
Thanks for the reply, sorry I coudn't reply yesterday.
Please see below for the sample data and desired result
Create Table dbo.Test_emailformat
(
[PersonID] [int] IDENTITY(1,1) NOT NULL,
company varchar(100) NOT NULL,
firstname varchar(50) NULL,
lastname varchar(50) NULL,
email varchar(200) NULL,
Constraint [PK_t_test_emailformat] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
))
INSERT INTO dbo.Test_emailformat (Company, firstname, lastname, email)
Select 'Test1','John','Smith','john.smith@test1.com.au'
UNION
Select 'Test1','Jason','Newell','jason.newell@test1.com.au'
UNION
Select 'Test1','Peter','Smith','peter@bigpond.com'
UNION
Select 'Test2','Jane','McDowell','jmcdowell@test2.com.au'
UNION
Select 'Test2','Jerry','Field','jfield@test2.com.au'
UNION
Select 'Test2','Michael','Williams','mwilliams@yahoo.com.au'
UNION
Select 'Test3','Lisa','Bone','lisa_bone@test3.com.au'
UNION
Select 'Test3','Mitchell','Read','mitchell_read@test2.com.au'
UNION
Select 'Department of Test','Matthew','Ireland','m.ireland@det.gov.au'
UNION
Select 'Department of Test','Julian','Smith','j_smith@det.gov.au'
Desired output
Company Email
Test1 john.smith@test1.com.au
Test2 jmcdowell@test2.com.au
Test3 lisabone@test3.com.au
Department of Testm_ireland@det.gov.au
I hope it is clear.
Thanks, Shilpa.
April 20, 2010 at 8:03 pm
hi Lowell, Bhuvnesh,
The whole point of this is we have acquired a database from third party which do not have email.
So I have to update the email column in the new database based on the existing data we have in our production database.
Say for example in our production database for company Freehills we have emails in format firstname.lastname@freehills.com.au then I have to update the email column for all freehills contacts in the new database using that format.
I hope I am making sense. To achieve that I am not sure how to approach the problem and where to start.
Is it possible to get a result which tells me what the email format is, like below
Company Email
Test1Firstname.Lastname@test1.com.au
Test2FirstLetterFirstnameLastname@test2.com.au
Test3FirstnameLastname@test3.com.au
Department of TestFirstLetterFirstname_Lastname@det.gov.au
April 20, 2010 at 10:56 pm
removed the code as i dint get the requirement clearly..
April 20, 2010 at 11:09 pm
hi,
Thanks for your reply.
The problem is not all companies will have the email format as firstname.lastname some may have firstname_lastname and so on. If you look at the sample data, for company Test1 the email format is jason.newell@test1.com.au and for test2 its jmcdowell@test2.com.au.
So both of them are different. I have done some brain storming and came up with few points they are listed below
•Company name will be present in the email address domain (ex: @test1.com.au)
•Some company’s may use abbreviation in the email address domain (ex : @det.gov.au)
•If company name matches the email address domain then it is an official email address, keeping in mind we have to exclude generic email address like (office@test1.com.au)
•First name or last name will be present in the official email address (spathi@test1.com.au)
Approach for T-SQL
Find emails with company and first name or last name in email domain.
For each company get the most used format, this will exclude the generic emails like office@test1.com.au
Let me know if I am making any sense to you. My brian is all hot I am craving for some cold coffee now.
April 21, 2010 at 12:12 am
Shilpa, this is such a biiiiiiiiggggg requirement i would say...:-D
but nonetheless not hard.. i had come up with a piece of code that might work for u.. kindly inform us if the below code worked for you!
;WITH MAIL_ID_FORMATS_CTE
(
[firstname.lastname],
[firstname_lastname],
[firstletter.lastname],
[firstletter_lastname],
[firstletterlastname],
[onlyfirstname],
[onlylastname]
)
AS
(
SELECT
LOWER(firstname+'.'+lastname) AS 'firstname.lastname'
,LOWER(firstname+'_'+lastname) AS 'firstname_lastname'
,LOWER(left(firstname,1)+'.'+lastname) AS 'firstletter.lastname'
,LOWER(left(firstname,1)+'_'+lastname) AS 'firstletter_lastname'
,LOWER(left(firstname,1)+lastname) AS 'firstletterlastname'
,CASE CHARINDEX(firstname,LEFT(email, CHARINDEX('@',email)-1))
WHEN 0 THEN NULL
ELSE LOWER(firstname)
END AS 'onlyfirstname'
,CASE CHARINDEX(lastname,LEFT(email, CHARINDEX('@',email)-1))
WHEN 0 THEN NULL
ELSE LOWER(lastname)
END AS 'onlylastname'
FROM dbo.Test_emailformat
)
SELECT MAIL_IDS.company, EMAIL_FORMAT =
CASE
WHEN MAIL_IDS.MAIL_ID_ONLY = CTE.[firstname.lastname]
THEN 'firstname.lastname'+MAIL_IDS.DOMAIN
WHEN MAIL_IDS.MAIL_ID_ONLY = CTE.[firstname_lastname]
THEN 'firstname_lastname'+ MAIL_IDS.DOMAIN
WHEN MAIL_IDS.MAIL_ID_ONLY = CTE.[firstletter.lastname]
THEN 'firstletter.lastname'+ MAIL_IDS.DOMAIN
WHEN MAIL_IDS.MAIL_ID_ONLY = CTE.[firstletter_lastname]
THEN 'firstletter_lastname'+ MAIL_IDS.DOMAIN
WHEN MAIL_IDS.MAIL_ID_ONLY = CTE.[firstletterlastname]
THEN 'firstletterlastname'+ MAIL_IDS.DOMAIN
WHEN MAIL_IDS.MAIL_ID_ONLY = CTE.[onlyfirstname]
THEN 'onlyfirstname'+ MAIL_IDS.DOMAIN
WHEN MAIL_IDS.MAIL_ID_ONLY = CTE.[onlylastname]
THEN 'onlylastname'+ MAIL_IDS.DOMAIN
ELSE 'UNKNOWN FORMAT'
END
FROM
MAIL_ID_FORMATS_CTE CTE
JOIN
(
SELECT company,
LEFT(email, CHARINDEX('@',email)-1) MAIL_ID_ONLY ,
RIGHT(email, CHARINDEX('@',REVERSE(email))) DOMAIN
FROM dbo.Test_emailformat
) MAIL_IDS
ON
(
MAIL_IDS.MAIL_ID_ONLY = CTE.[firstname.lastname] OR
MAIL_IDS.MAIL_ID_ONLY = CTE.[firstname_lastname] OR
MAIL_IDS.MAIL_ID_ONLY = CTE.[firstletter.lastname] OR
MAIL_IDS.MAIL_ID_ONLY = CTE.[firstletter_lastname] OR
MAIL_IDS.MAIL_ID_ONLY = CTE.[firstletterlastname] OR
MAIL_IDS.MAIL_ID_ONLY = CTE.[onlyfirstname] OR
MAIL_IDS.MAIL_ID_ONLY = CTE.[onlylastname]
)
I did notice one flaw, that the first row is repeating twice.. i cant figure that out now.. may be a "group by" of the final result will give u unique set of records..
Tell us here if this worked for you..:-)
Cheers!!
C'est Pras!!
April 21, 2010 at 6:40 am
sharonrao123 (4/20/2010)
My brian is all hot I am craving for some cold coffee now.
You wanted a cold-coffee, and here is your coldcoffee presenting you with a cold code :-D!! check and tell us if that worked 😎
April 21, 2010 at 4:53 pm
hi,
The code worked perfectly, wow you are a start. Thanks a lot for your time.
Shlipa.
April 21, 2010 at 4:54 pm
Cold coffee made me coolllll 😀
April 21, 2010 at 8:08 pm
sharonrao123 (4/21/2010)
Cold coffee made me coolllll 😀
:blush: Thanks, Shilpa!!! Glad that the code helped and happy that your issue is resolved 😉
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply