how to filter domain name from email address

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to filter domain name from email address

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-26 : 15:06:10

hello,
How do i filter domain name(s) from email address.

i have a table with _from, _to, _cc, _bcc, send_dom, rec_dom
i have to filter all email domain name from _from filed and update into send_dom filed. If _form has more than one email address but its different domain name, update all diff domain name.

And update rec_dom from _to, _cc, and _bcc fields

e.g
if i have aa@abc.com;bcd@aaa.com
it will be update : abc.com;aaa.com

SELECT RIGHT(_from, CHARINDEX('@', REVERSE(_from))-1) AS [Domain Name]
FROM tblemp

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-26 : 15:35:18

Split the String on the semicolon ;
Use the CHARINDEX function to find the position of the @ symbol.
Use the SUBSTRING function to get the string after the @ symbol (which will rely on CHARINDEX).

Something like this for getting one email? (note untested)

SUBSTRING(yourtable.email, CHARINDEX('@', yourtable.email)

r&r

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-26 : 15:39:59

thanks for replay.
i got the error because i have some records like this:
person name; <aaa@bbb.com;mnop@xyz>

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-26 : 15:40:50

Is there a limit on the number of e-mails in that column...Max of 2 or something likat that?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-26 : 15:43:54

no its not limit. it could be 1 or more
i guess, we need function to check how many '@' to filter domain name.
Anybody has function to filter the domain name from email address?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113963

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-26 : 16:46:46

I don't think SQL 2K5 comes with an out of the box SPLIT function so you would need to write one yourself. I am sure there is more than one way to do it.

Here is a decent link to help get you started. It seems to address what you want. This is also a good way for you to learn.
[url]http://www.4guysfromrolla.com/webtech/031004-1.shtml[/url]

I used this website and I have a split function created in our server.
It is very useful for us but not quite what you are looking for.

happy hunting !

r&r

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-26 : 16:58:40

thanks revdnrdy,
We don't know how many email address are store in a record, i will try to create split function. If anybody has let us know

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-26 : 21:53:18

Guys, anybody got the solution?

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-02-27 : 02:30:36

Hi,
See following example. Hopefully this is what you want, if not please provide some sample data which is causing the problem.



/*
CREATE TABLE EMAILS (Email varchar(1000))
INSERT INTO Emails SELECT
'mangal.pardeshi@indiamvps.net;mangal@my.com'UNION ALL SELECT
'aa@abc.com;bcd@aaa.com' UNION ALL SELECT
'<aaa@bbb.com;mnop@xyz>'
*/

;WITH cte AS (
    SELECT 
        
        CAST('<i>' + REPLACE(REPLACE(REPLACE(email,'<',''),'>',''), ';', '</i><i>') + '</i>' AS XML) AS EmailId
    FROM Emails
)
,Mangal as
(
SELECT 

    SUBSTRING(x.i.value('.', 'VARCHAR(1000)')
				,CHARINDEX('@',x.i.value('.', 'VARCHAR(1000)'))+1,
				LEN(x.i.value('.', 'VARCHAR(1000)'))) as Eid
FROM cte
CROSS APPLY emailid.nodes('//i') x(i)
)
select CASE WHEN CHARINDEX('.',EId) >0 THEN LEFT(EId,CHARINDEX('.',EId)-1)
			ELSE EID END AS domain
 from mangal

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-27 : 07:53:34

thanks Mangal Pardeshi

But if i have multile same domain it wont work.
e.g i have data lik:
/*
CREATE TABLE EMAILS (Email varchar(1000))
INSERT INTO Emails SELECT
'mangal.pardeshi@indiamvps.net;mangal@my.com;rara@my.com'UNION ALL SELECT
'aa@abc.com;bcd@aaa.com;aas@abc.com;dd@aaa.com' UNION ALL SELECT
'<aaa@bbb.com;mnop@xyz;aaa@abc.com>'
*/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 04:29:04

[code]Declare @Text Varchar(100),@delimiter NVARCHAR(5)
Set @Text = 'mangal.pardeshi@indiamvps.net;mangal@my.com;rara@my.com'
set @delimiter = ';'
Declare @textXml Xml
Select @TextXml = Cast('<d>' + Replace(@Text, @Delimiter,'</d><d>') + '</d>' As Xml );
SELECT @Text as email
SELECT data,substring(data,charindex('@',data)+1,len(data)) as domain from
(
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
) as t

[/code]

Madhivanan

Failing to plan is Planning to fail

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-02 : 04:41:40

quote:
Originally posted by rudba

thanks Mangal Pardeshi

But if i have multile same domain it wont work.
e.g i have data lik:

Can you explain what is not working? I'm getting the output. You wan only distinct domains?

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-02 : 06:03:54

[code]
try this too rudba
SELECT
SUBSTRING(s.Email,charindex(';',s.Email,v.number)+1,abs(charindex(';',s.Email,charindex(';',s.Email,v.number)+1)-1-charindex(';',s.Email,v.number)))as email
into #temp
FROM @EMAILS AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
and v.number > 0
and v.number <= len(s.Email)
WHERE substring(';' + s.Email, v.number, 1) = ';'

select case when charindex('.',email,charindex('@',email,1)+1) > 0
then substring(email, charindex('@',email,1)+1,charindex('.',email,charindex('@',email,1)+1) -charindex('@',email,1)-1)
else substring(email, charindex('@',email,1)+1,len(email)) end as domain
from #temp

drop table #temp
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources