How to use LIKE '@%' to find Email address domains in database

Question

I want to query our database to find how many different domains customers use for their email address.

Is this the correct syntax?

SELECT PR.EmailAddress FROM PatronRegistration PR with (nolock) WHERE PR.EmailAddress LIKE '@%'

Because 0 rows are returned.

"@" is used to preceed variables but I thought this would work to within the single quotes

Answer 1

0

Try

LIKE '%@%'

If you're matching full email addresses, you'll need the wildcard before and after the @ sign.

--edit-- - If you're looking for more quantitative information, then this query might be of use:

select SUBSTRING(EmailAddress, charindex('@', EmailAddress), 999), COUNT(*)
from PatronRegistration
where EmailAddress LIKE '%@%'
group by SUBSTRING(EmailAddress, charindex('@', EmailAddress), 999)

This will return the domain names and the count of the number of times they are used.

answered Mar 15 '12 at 03:59 PM

ThomasRushton ♦
29.4k ● 6 ● 9 ● 36

(comments are locked)

Answer 2

0

Yes Tim, that's exactly what I am after. Thanks Thomas for the script. Now I have new tool "substring" with which to work.

Jon,Yes Tim I am looking for the number of instances of every domain that is in our database. Thanks Thomas, that's exactly what I needed.

Jon

answered Mar 26 '12 at 08:02 PM

jonlellelid
21 ● 1 ● 1 ● 1

If Thomas' answer was what you needed, please take a few moments to accept his answer to future visitors to the site will know that was then answer you were after.

Mar 26 '12 at 08:04 PM KenJ

(comments are locked)

Answer 3

0

That is only looking for an email address that starts with @. Are you just wanting the domains to be returned such as .com, .org, .edu?

answered Mar 15 '12 at 04:01 PM

Tim
31.5k ● 20 ● 31 ● 116

(comments are locked)

How to use LIKE '@%' to find Email address domains in database

Follow this question

Related Questions