Query Help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-08-05 : 14:02:04

I need to query out all invalid emails. Please see the desire result below.

Thank you in advance.
--SQL2008

IF OBJECT_ID('Tempdb.dbo.#Temp ', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
CounterId INT NOT NULL,
EMail VARCHAR(50) NULL
)
GO

INSERT INTO #Temp VALUES (1, 'SmithM'), (2, 'LeeH@'), (3, 'LeeH@MHCDomain'),
(4, 'NguyenJ@MHCDomain.com');

go

SELECT *
FROM #Temp;
GO

-- Desire result:
CounterId EMail
----------- -----------------------
4 NguyenJ@MHCDomain.com

--Testing..

SELECT CHARINDEX('@', Email),
*
FROM #Temp
--WHERE (CHARINDEX('@', Email) = 0)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-05 : 14:32:14

What are your business rules for a valid email address?

Here is one way:

SELECT *
FROM #Temp
WHERE EMail LIKE '_%@%_.___'

Possibly this:

[code]SELECT *
FROM #Temp
WHERE EMail LIKE '_%@%_.__%'

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-08-05 : 14:48:42

There is no business rules for this. Just complete email like the one show below.

SmithD@MHCDomain.com

Thanks.

quote:
Originally posted by Lamprey

What are your business rules for a valid email address?

Here is one way:
SELECT *
FROM #Temp
WHERE EMail LIKE '_%@%_.___'
Possibly this:
[code]SELECT *
FROM #Temp
WHERE EMail LIKE '_%@%_.__%'

jerometang
Starting Member

1 Post

Posted - 2010-08-08 : 22:22:21

you can try my idea!

IF OBJECT_ID('Tempdb.dbo.#Temp ', 'u') IS NOT NULL
DROP TABLE #Temp
GO
CREATE TABLE #Temp
(
CounterId INT NOT NULL,
EMail VARCHAR(50) NULL
)
GO

INSERT INTO #Temp VALUES (1, 'SmithM'), (2, 'LeeH@'), (3, 'LeeH@MHCDomain'),
(4, 'NguyenJ@MHCDomain.com'),(5, 'NguyenJ@MH@CDomain.com');

go

select * from #Temp

select * from #Temp
where
CHARINDEX('@',email,1)>0
and CHARINDEX('@',email,CHARINDEX('@',email,1)+1)=0
and CHARINDEX('.',email,CHARINDEX('@',email,1)+1)>0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources