x

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

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
more ▼

asked Mar 15, 2012 at 03:56 PM in Default

jonlellelid gravatar image

jonlellelid
51 5 6 8

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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.
more ▼

answered Mar 15, 2012 at 03:59 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Mar 26, 2012 at 08:02 PM

jonlellelid gravatar image

jonlellelid
51 5 6 8

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, 2012 at 08:04 PM KenJ
(comments are locked)
10|1200 characters needed characters left
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?
more ▼

answered Mar 15, 2012 at 04:01 PM

Tim gravatar image

Tim
36.4k 38 41 139

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x22

asked: Mar 15, 2012 at 03:56 PM

Seen: 2241 times

Last Updated: Mar 26, 2012 at 08:04 PM