Trim Email Address down to domain only

  • Thread starter Thread starter Steve Roberts
  • Start date Start date
S

Steve Roberts

Adp 2003 + SQL 7.0

I am trying to create a list of unique domains that we send email to for our
spam filter. The column I am pulling from has the full (e-mail address removed) and I
need to create a query that just has domain.com in it.

SELECT DISTINCT EMAIL_ADDRESS
FROM dbo.CLIENT_CONTACT
WHERE (EMAIL_ADDRESS <> '""')

Thanks in advance..
Steve
 
I had to be easier than I was making it. Thanks!


Douglas J. Steele said:
Mid([EMAIL_ADDRESS], InStr([EMAIL_ADDRESS], "@") + 1)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



Steve Roberts said:
Adp 2003 + SQL 7.0

I am trying to create a list of unique domains that we send email to for our
spam filter. The column I am pulling from has the full (e-mail address removed)
and I
need to create a query that just has domain.com in it.

SELECT DISTINCT EMAIL_ADDRESS
FROM dbo.CLIENT_CONTACT
WHERE (EMAIL_ADDRESS <> '""')

Thanks in advance..
Steve
Click to expand...
Click to expand...
 
Not sure if this will work with an ADP.

SELECT DISTINCT
RIGHT(EMail_ADDRESS,InStr(1,EMail_Address,"@")+1) as DomainOnly
FROM dbo.Client_Contact
WHERE Email_Address Is Not Null
 
I spoke too soon. Both solutions produce an ADO error: INSTR is not a
recognized function name.

Any Ideas?
 
WHOOPS that should have been MID, not RIGHT. Oh well, glad to see Doug Steele
gave you the correct answer.
 
Sorry: I totally missed the ADP part.

I think you can use:

SubString(EMail_Address, PatIndex("@", EMail_Address) + 1,
Len(EMail_Address) - PatIndex("@", EMail_Address))

If EMail_Address is Char (as opposed to Varchar), you'll probably have to
use Len(RTrim(EMail_Address)) instead.
 
When I check this query I get an invalid column name '. error. I verified
the spelling of everything and that looks good. I looked up all of the
commands that arebeign used in the SQL BOL and I can't figure out what is
wrong.
Here is how I inserted your code:

SELECT DISTINCT
SUBSTRING(EMAIL_ADDRESS, PATINDEX([@], EMAIL_ADDRESS) + 1,
LEN(EMAIL_ADDRESS) - PATINDEX([@], EMAIL_ADDRESS))
FROM dbo.CLIENT_CONTACT

Thanks again

steve
 
What's with the [@]? The code I posted used "@"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve Roberts said:
When I check this query I get an invalid column name '. error. I verified
the spelling of everything and that looks good. I looked up all of the
commands that arebeign used in the SQL BOL and I can't figure out what is
wrong.
Here is how I inserted your code:

SELECT DISTINCT
SUBSTRING(EMAIL_ADDRESS, PATINDEX([@], EMAIL_ADDRESS) + 1,
LEN(EMAIL_ADDRESS) - PATINDEX([@], EMAIL_ADDRESS))
FROM dbo.CLIENT_CONTACT

Thanks again

steve




Douglas J. Steele said:
Sorry: I totally missed the ADP part.

I think you can use:

SubString(EMail_Address, PatIndex("@", EMail_Address) + 1,
Len(EMail_Address) - PatIndex("@", EMail_Address))

If EMail_Address is Char (as opposed to Varchar), you'll probably have to
use Len(RTrim(EMail_Address)) instead.
Click to expand...
Click to expand...
 
Hmm so much for copy paste! It still gives me the error. I keep looking at
it and I don't see any problems.

SELECT DISTINCT SubString(EMail_Address, PatIndex("@", EMail_Address) + 1,
Len(EMail_Address) - PatIndex("@", EMail_Address))
FROM dbo.CLIENT_CONTACT

Thanks

Steve



Douglas J. Steele said:
What's with the [@]? The code I posted used "@"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve Roberts said:
When I check this query I get an invalid column name '. error. I verified
the spelling of everything and that looks good. I looked up all of the
commands that arebeign used in the SQL BOL and I can't figure out what is
wrong.
Here is how I inserted your code:

SELECT DISTINCT
SUBSTRING(EMAIL_ADDRESS, PATINDEX([@], EMAIL_ADDRESS) + 1,
LEN(EMAIL_ADDRESS) - PATINDEX([@], EMAIL_ADDRESS))
FROM dbo.CLIENT_CONTACT

Thanks again

steve




Douglas J. Steele said:
Sorry: I totally missed the ADP part.

I think you can use:

SubString(EMail_Address, PatIndex("@", EMail_Address) + 1,
Len(EMail_Address) - PatIndex("@", EMail_Address))

If EMail_Address is Char (as opposed to Varchar), you'll probably have to
use Len(RTrim(EMail_Address)) instead.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I spoke too soon. Both solutions produce an ADO error: INSTR is not a
recognized function name.

Any Ideas?

Not sure if this will work with an ADP.

SELECT DISTINCT
RIGHT(EMail_ADDRESS,InStr(1,EMail_Address,"@")+1) as DomainOnly
FROM dbo.Client_Contact
WHERE Email_Address Is Not Null

Steve Roberts wrote:

Adp 2003 + SQL 7.0

I am trying to create a list of unique domains that we send email
to
for
our
spam filter. The column I am pulling from has the full (e-mail address removed)
and I
need to create a query that just has domain.com in it.

SELECT DISTINCT EMAIL_ADDRESS
FROM dbo.CLIENT_CONTACT
WHERE (EMAIL_ADDRESS <> '""')

Thanks in advance..
Steve
Click to expand...
Click to expand...
Click to expand...
 
Try using single quotes (apostrophes) vice double quotes (quote marks).

Steve said:
Hmm so much for copy paste! It still gives me the error. I keep looking at
it and I don't see any problems.

SELECT DISTINCT SubString(EMail_Address, PatIndex("@", EMail_Address) + 1,
Len(EMail_Address) - PatIndex("@", EMail_Address))
FROM dbo.CLIENT_CONTACT

Thanks

Steve

Douglas J. Steele said:
What's with the [@]? The code I posted used "@"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve Roberts said:
When I check this query I get an invalid column name '. error. I verified
the spelling of everything and that looks good. I looked up all of the
commands that arebeign used in the SQL BOL and I can't figure out what is
wrong.
Here is how I inserted your code:

SELECT DISTINCT
SUBSTRING(EMAIL_ADDRESS, PATINDEX([@], EMAIL_ADDRESS) + 1,
LEN(EMAIL_ADDRESS) - PATINDEX([@], EMAIL_ADDRESS))
FROM dbo.CLIENT_CONTACT

Thanks again

steve




Sorry: I totally missed the ADP part.

I think you can use:

SubString(EMail_Address, PatIndex("@", EMail_Address) + 1,
Len(EMail_Address) - PatIndex("@", EMail_Address))

If EMail_Address is Char (as opposed to Varchar), you'll probably have to
use Len(RTrim(EMail_Address)) instead.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I spoke too soon. Both solutions produce an ADO error: INSTR is not a
recognized function name.

Any Ideas?

Not sure if this will work with an ADP.

SELECT DISTINCT
RIGHT(EMail_ADDRESS,InStr(1,EMail_Address,"@")+1) as DomainOnly
FROM dbo.Client_Contact
WHERE Email_Address Is Not Null

Steve Roberts wrote:

Adp 2003 + SQL 7.0

I am trying to create a list of unique domains that we send email
to
for
our
spam filter. The column I am pulling from has the full (e-mail address removed)
and I
need to create a query that just has domain.com in it.

SELECT DISTINCT EMAIL_ADDRESS
FROM dbo.CLIENT_CONTACT
WHERE (EMAIL_ADDRESS <> '""')

Thanks in advance..
Steve
Click to expand...
Click to expand...
Click to expand...
 
Mental lapse (again!) on my part. SQL Server uses single quotes, not double
quotes.

Try

SELECT DISTINCT SubString(EMail_Address, PatIndex('@', EMail_Address) + 1,
Len(EMail_Address) - PatIndex('@', EMail_Address))
FROM dbo.CLIENT_CONTACT

You could also use CharIndex instead of PatIndex

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve Roberts said:
Hmm so much for copy paste! It still gives me the error. I keep looking at
it and I don't see any problems.

SELECT DISTINCT SubString(EMail_Address, PatIndex("@", EMail_Address) + 1,
Len(EMail_Address) - PatIndex("@", EMail_Address))
FROM dbo.CLIENT_CONTACT

Thanks

Steve



Douglas J. Steele said:
What's with the [@]? The code I posted used "@"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve Roberts said:
When I check this query I get an invalid column name '. error. I verified
the spelling of everything and that looks good. I looked up all of the
commands that arebeign used in the SQL BOL and I can't figure out what is
wrong.
Here is how I inserted your code:

SELECT DISTINCT
SUBSTRING(EMAIL_ADDRESS, PATINDEX([@], EMAIL_ADDRESS) + 1,
LEN(EMAIL_ADDRESS) - PATINDEX([@], EMAIL_ADDRESS))
FROM dbo.CLIENT_CONTACT

Thanks again

steve




Sorry: I totally missed the ADP part.

I think you can use:

SubString(EMail_Address, PatIndex("@", EMail_Address) + 1,
Len(EMail_Address) - PatIndex("@", EMail_Address))

If EMail_Address is Char (as opposed to Varchar), you'll probably
Click to expand...
have
to
use Len(RTrim(EMail_Address)) instead.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I spoke too soon. Both solutions produce an ADO error: INSTR is not a
recognized function name.

Any Ideas?

Not sure if this will work with an ADP.

SELECT DISTINCT
RIGHT(EMail_ADDRESS,InStr(1,EMail_Address,"@")+1) as DomainOnly
FROM dbo.Client_Contact
WHERE Email_Address Is Not Null

Steve Roberts wrote:

Adp 2003 + SQL 7.0

I am trying to create a list of unique domains that we send email
to
for
our
spam filter. The column I am pulling from has the full (e-mail address removed)
and I
need to create a query that just has domain.com in it.

SELECT DISTINCT EMAIL_ADDRESS
FROM dbo.CLIENT_CONTACT
WHERE (EMAIL_ADDRESS <> '""')

Thanks in advance..
Steve
Click to expand...
Click to expand...
Click to expand...
 
Back
Top