Query Help.
in [Programming SQL]
|
From: LN on 5 Aug 2010 14:03 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(a)MHCDomain'), (4, 'NguyenJ(a)MHCDomain.com'); go SELECT * FROM #Temp; GO -- Desire result: CounterId EMail ----------- ----------------------- 4 NguyenJ(a)MHCDomain.com --Testing.. SELECT CHARINDEX('@', Email), * FROM #Temp --WHERE (CHARINDEX('@', Email) = 0)
From: Gert-Jan Strik on 5 Aug 2010 14:26 What is your definition of a valid e-mail address? The query below will give the correct result for the your test set. SELECT * FROM #Temp WHERE EMail LIKE '%_@_%.%__' It only selects EMail where there is at least one character before the "@" sign, then at least another character before the ".", followed by at least two other characters. -- Gert-Jan LN wrote: > > 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(a)MHCDomain'), > (4, 'NguyenJ(a)MHCDomain.com'); > > go > > SELECT * > FROM #Temp; > GO > > -- Desire result: > CounterId EMail > ----------- ----------------------- > 4 NguyenJ(a)MHCDomain.com > > --Testing.. > > SELECT CHARINDEX('@', Email), > * > FROM #Temp > --WHERE (CHARINDEX('@', Email) = 0)
From: LN on 5 Aug 2010 16:19 Hi Gert-Jan, How can your query prevent space in between CounterId 5 and 6. There is no definition of valid email, I am counting on users enter his/her info correct through the application but there are some cases users are not doing that. That's why I have to check as much as possible on the back end. Again, Thank you for your time. INSERT INTO #Temp VALUES (1, 'SmithM'), (2, 'LeeH@'), (3, 'LeeH(a)MHCDomain'), (4, 'NguyenJ(a)MHCDomain.com'), (5, 'NLe(a)MHCDomain ..com'), (6, 'WLu@ kondaur.com'); go SELECT * FROM #Temp; GO CounterId EMail ----------- -------------------------- 5 NLe(a)MHCDomain .com 6 WLu@ kondaur.com -- Desire result: CounterId EMail ----------- ----------------------- 4 NguyenJ(a)MHCDomain.com SELECT * FROM DailyExceptionValReport WHERE EMail LIKE '%_@_%.%__' --and CHARINDEX('', LTRIM(RTRIM(EMail))) = 0; "Gert-Jan Strik" wrote: > What is your definition of a valid e-mail address? > > The query below will give the correct result for the your test set. > > SELECT * > FROM #Temp > WHERE EMail LIKE '%_@_%.%__' > > It only selects EMail where there is at least one character before the > "@" sign, then at least another character before the ".", followed by at > least two other characters. > -- > Gert-Jan > > > LN wrote: > > > > 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(a)MHCDomain'), > > (4, 'NguyenJ(a)MHCDomain.com'); > > > > go > > > > SELECT * > > FROM #Temp; > > GO > > > > -- Desire result: > > CounterId EMail > > ----------- ----------------------- > > 4 NguyenJ(a)MHCDomain.com > > > > --Testing.. > > > > SELECT CHARINDEX('@', Email), > > * > > FROM #Temp > > --WHERE (CHARINDEX('@', Email) = 0) > . >
From: Gene Wirchenko on 5 Aug 2010 16:31 On Thu, 5 Aug 2010 13:19:03 -0700, LN <LN(a)discussions.microsoft.com> wrote: >How can your query prevent space in between CounterId 5 and 6. There is no >definition of valid email, I am counting on users enter his/her info correct The format is defined in RFC 5322. [snip] Sincerely, Gene Wirchenko
From: Gert-Jan Strik on 5 Aug 2010 19:08 The LIKE string cannot be changed to exclude spaces, so that would require a predicate like the one you commented out, or something like this: SELECT * FROM DailyExceptionValReport WHERE EMail LIKE '%_@_%.%__' AND EMail NOT LIKE '% %' If you are worried about leading or trailing spaces, then you should simply run an UPDATE statement with LTRIM(RTRIM(...)) to remove them permanently. -- Gert-Jan LN wrote: > > Hi Gert-Jan, > > How can your query prevent space in between CounterId 5 and 6. There is no > definition of valid email, I am counting on users enter his/her info correct > through the application but there are some cases users are not doing that. > That's why I have to check as much as possible on the back end. > > Again, Thank you for your time. > > INSERT INTO #Temp VALUES (1, 'SmithM'), (2, 'LeeH@'), (3, 'LeeH(a)MHCDomain'), > (4, 'NguyenJ(a)MHCDomain.com'), (5, 'NLe(a)MHCDomain > .com'), > (6, 'WLu@ kondaur.com'); > > go > > SELECT * > FROM #Temp; > GO > > CounterId EMail > ----------- -------------------------- > 5 NLe(a)MHCDomain .com > 6 WLu@ kondaur.com > > -- Desire result: > CounterId EMail > ----------- ----------------------- > 4 NguyenJ(a)MHCDomain.com > > SELECT * > FROM DailyExceptionValReport > WHERE EMail LIKE '%_@_%.%__' > --and CHARINDEX('', LTRIM(RTRIM(EMail))) = 0; > > "Gert-Jan Strik" wrote: > > > What is your definition of a valid e-mail address? > > > > The query below will give the correct result for the your test set. > > > > SELECT * > > FROM #Temp > > WHERE EMail LIKE '%_@_%.%__' > > > > It only selects EMail where there is at least one character before the > > "@" sign, then at least another character before the ".", followed by at > > least two other characters. > > -- > > Gert-Jan > > > > > > LN wrote: > > > > > > 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(a)MHCDomain'), > > > (4, 'NguyenJ(a)MHCDomain.com'); > > > > > > go > > > > > > SELECT * > > > FROM #Temp; > > > GO > > > > > > -- Desire result: > > > CounterId EMail > > > ----------- ----------------------- > > > 4 NguyenJ(a)MHCDomain.com > > > > > > --Testing.. > > > > > > SELECT CHARINDEX('@', Email), > > > * > > > FROM #Temp > > > --WHERE (CHARINDEX('@', Email) = 0) > > . > >
|
Pages: 1 Prev: Simple query question Next: Help querying for a specific date |