>HI
>
>I am trying to join two tables in a query based on the domain name in the email
>
>so I have
>
>

>select * from account a
>JOIN contact c
>ON
>right(c.[EMailAddress1], charindex('@', reverse(c.[EMailAddress1]))-1)  = right(a.[EMailAddress1], charindex('@', reverse(a.[EMailAddress1]))-1)
>

>
>which gives me the domain name in both emails.
>
>but when I run the query I get a failure when an email address has no @ sign ( few are empty)
>
>any idea whats the best way to deal with that in the query join.
>
>Thanks
>
>Nick Mason

The common solution is to use NULLIF function, e.g.

right(c.EmailAddress1, NULLIF(charindex('@', reverse(c.EMailAddress1)), 0) - 1) = ...