December 22, 2002 at 10:24 am
Thought this would be easy, but I can't seem to find it in any of the books. How can I check to see if there are multiple characters in a field.... Specifically I am trying to find all the email addresses that have more than 1 '@'..... Thanks guys.
December 22, 2002 at 11:15 am
Not sure of anything specific to do that in general but for your case you can do
SELECT * FROM tblX WHERE COLEMAIL LIKE '%@%@%'
Just be aware it may take a bit on a large table.
Edited by - antares686 on 12/22/2002 11:23:44 AM
December 22, 2002 at 3:31 pm
Imho, best way to count number of occurences of a character in a string is this:
SELECT LEN('abc@abc@def.com') - LEN(REPLACE('abc@abc@def.com', '@', ''))
This will tell you that there are two @-signs in the string.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
December 22, 2002 at 4:05 pm
I like the latter solution better (mostly because I've used it before, not really a good reason!).
Andy
December 26, 2002 at 4:45 pm
To add another solution, you could use :
SELECT CHARINDEX('@', column,
CHARINDEX('@', column)
)
If this returns 0 (zero), it means that there was (at most) one '@' in your string.
December 30, 2002 at 3:39 pm
Here's a solution I believe I found on this forum. This one checks for other common errors that people make when entering an email address.
--update tblevent set unclassifiedemail = 'Unknown@unknown.com'
Select *
From tblevent
WHERE NOT
(
CHARINDEX(' ',LTRIM(RTRIM([UnclassifiedEmail]))) = 0
AND LEFT(LTRIM([UnclassifiedEmail]),1) <> '@'
AND RIGHT(RTRIM([UnclassifiedEmail]),1) <> '.'
AND CHARINDEX('.',[UnclassifiedEmail],CHARINDEX('@',[UnclassifiedEmail])) - CHARINDEX('@',[UnclassifiedEmail]) > 1
AND LEN(LTRIM(RTRIM([UnclassifiedEmail]))) - LEN(REPLACE(LTRIM(RTRIM([UnclassifiedEmail])),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([UnclassifiedEmail])))) >= 3
AND (CHARINDEX('.@',[UnclassifiedEmail]) = 0 AND CHARINDEX('..',[UnclassifiedEmail]) = 0)
)
ALTER TABLE [dbo].[tblevent]
WITH CHECK ADD
CONSTRAINT [EmailValidator]
CHECK
(
CHARINDEX(' ',LTRIM(RTRIM([UnclassifiedEmail]))) = 0 -- No embedded spaces
AND LEFT(LTRIM([UnclassifiedEmail]),1) <> '@' -- '@' can't be the first character of an email address
AND RIGHT(RTRIM([UnclassifiedEmail]),1) <> '.' -- '.' can't be the last character of an email address
AND CHARINDEX('.',[UnclassifiedEmail],CHARINDEX('@',[UnclassifiedEmail])) - CHARINDEX('@',[UnclassifiedEmail]) > 1 -- There must be a '.' after '@'
AND LEN(LTRIM(RTRIM([UnclassifiedEmail]))) - LEN(REPLACE(LTRIM(RTRIM([UnclassifiedEmail])),'@','')) = 1 -- Only one '@' sign is allowed
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([UnclassifiedEmail])))) >= 3 -- Domain name should end with at least 2 character extension
AND (CHARINDEX('.@',[UnclassifiedEmail]) = 0 AND CHARINDEX('..',[UnclassifiedEmail]) = 0) -- can't have patterns like '.@' and '..'
)
GO
SELECTRIGHT(UnclassifiedEmail, CHARINDEX('@', REVERSE(UnclassifiedEmail))-1) AS [Domain Name], COUNT(UnclassifiedEmail) AS [Number of Subscribers]
FROM dbo.tblevent
GROUP BY RIGHT(UnclassifiedEmail, CHARINDEX('@', REVERSE(UnclassifiedEmail))-1)
ORDER BY [Number of Subscribers] DESC, [Domain Name]
Edward M. Sokolove
Edward M. Sokolove
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply