Re: Trim Email Address down to domain only

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 10/28/04 

Date: Wed, 27 Oct 2004 20:05:10 -0400

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
http://I.Am/DougSteele
(no e-mails, please!)
"Steve Roberts" <Stever@Discussiongroups.com> wrote in message
news:OtEUtpHvEHA.1928@TK2MSFTNGP10.phx.gbl...
> 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" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:Of%239ozGvEHA.1308@TK2MSFTNGP09.phx.gbl...
> > What's with the [@]? The code I posted used "@"
> >
> > -- 
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> >
> > "Steve Roberts" <Stever@Discussiongroups.com> wrote in message
> > news:ejcTRKDvEHA.3896@TK2MSFTNGP09.phx.gbl...
> >> 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" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
message
> >> news:OlesEE8uEHA.3228@TK2MSFTNGP12.phx.gbl...
> >> > 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
> >> > http://I.Am/DougSteele
> >> > (no e-mails, please!)
> >> >
> >> >
> >> >
> >> > "Steve Roberts" <Stever@Discussiongroups.com> wrote in message
> >> > news:OnNeGF7uEHA.3276@TK2MSFTNGP15.phx.gbl...
> >> >> I spoke too soon. Both solutions produce an ADO error:  INSTR is not
a
> >> >> recognized function name.
> >> >>
> >> >> Any Ideas?
> >> >>
> >> >> "John Spencer (MVP)" <spencer4@comcast.net> wrote in message
> >> >> news:417ED2AC.62ACBB0D@comcast.net...
> >> >> > 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
> > user@domain.com
> >> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Relevant Pages

  • RE: Problem with query causing Access to go to white screen
    ... Access not to refresh/repaint and go white? ... Dave Hargis, Microsoft Access MVP ... When you say "copy the Access DB to the SQL Server I am running the query off" ...
    (microsoft.public.access.modulesdaovba)
  • Re: One Web Service updates SQL, the other cant
    ... Here is the dialog between Tom and I. ... Columnist, SQL Server Professional ... "Steve Ricketts" wrote in message ... I guess its because this is my first Web Service and I> just used the access methods I knew. ...
    (microsoft.public.vb.database.ado)
  • RE: Access limitations/suitablity
    ... Sorry for my delayed response. ... I think Tony has some great thoughts on upsizing as to when and how. ... Dave Hargis, Microsoft Access MVP ... performance they had to convert to something like SQL Server? ...
    (microsoft.public.access.gettingstarted)
  • Re: SQL Tables
    ... Microsoft Access MVP ... "Graham R Seach" wrote: ... albeit transferred to SQL Server. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: getting into access, can you point me in the right direction?
    ... Your ignorance is exceeded only by your offensive attitude. ... I have no need for training wheels, but you really need to get some ... using SQL Server as the data source is a good way to ... Dave Hargis, Microsoft Access MVP ...
    (microsoft.public.access.gettingstarted)