Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

E-Mail Validator Expand / Collapse
Author
Message
Posted Friday, November 20, 2009 1:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 341, Visits: 688
Comments posted to this topic are about the item E-Mail Validator
Post #822161
Posted Wednesday, December 9, 2009 6:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:18 PM
Points: 48, Visits: 202
Very nice.

I would make a couple of suggestions though. To avoid the loop looking at each individual character, use regular expressions to look for invalid characters:

IF (
@vParameter_Length = 0
OR CHARINDEX ('@', @vParameter) < 2
OR CHARINDEX ('.', @vParameter) < 2
OR CHARINDEX ('.@', @vParameter) <> 0
OR CHARINDEX ('..', @vParameter) <> 0
OR CHARINDEX ('.', REVERSE (@vParameter)) < 3
OR CHARINDEX ('.', @vParameter, CHARINDEX ('@',@vParameter)) = 0
OR RIGHT (@vParameter,1) = '@'
OR @vParameter LIKE '%@%@%'
OR @vParameter LIKE '%[!-*]%'
OR @vParameter LIKE '%,%'
OR @vParameter LIKE '%/%'
OR @vParameter LIKE '%[:-?]%'
OR @vParameter LIKE '%[[-^]%'
OR @vParameter LIKE '%`%'
OR @vParameter LIKE '%[{-~]%'

)

Then if you are going to do it on an entire table, you can avoid the function altogether which would probably bring your SQL Server to its knees to have a function in the where clause of a select against a million row table. This query ran against an 800,000+ row table in about 4 seconds.
Post #831322
Posted Wednesday, December 9, 2009 8:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:29 AM
Points: 3, Visits: 546
I found this code in Internet some time ago.

I modify to Sqlserver


create FUNCTION IsValidEmail
(@Email varchar(100))



RETURNS bit
AS
BEGIN

Declare @Long Int,
@Pos Int,
@Bueno Bit,
@Ext varchar(1000),
@Letras varchar(100),
@I Int

Set @Letras = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ@.-_0123456789'
Set @Email = rtrim(ltrim(@Email))
Set @Long = Len(@Email)
Set @Pos = CharIndex('@',@Email)
Set @Bueno = 0

If not (@Long < 6 -- Muy corto (very short)
Or @Pos = 0 -- No hay arrobas (no @s)
Or CharIndex('@',@Email,CharIndex('@',@Email)+1) > 0 -- dos o mas arrobas (more than 1 @)
Or CharIndex(left(@Email,1),'@.-_') > 0 -- No empieza por '@.-_'
Or CharIndex(right(@Email,1),'@.-_') > 0 -- No termina en '@.-_'
Or CharIndex('@.',@Email) > 0
or CharIndex('..',@Email) > 0
or CharIndex('.@',@Email) > 0) -- @. ó ..

Begin
Set @Ext = '.COM.EDU.GOV.NET.BIZ.ORG.TV.INFO'
+ '.AF.AL.DZ.As.AD.AO.AI.AQ.AG.AP.AR.AM.AW.AU.AT.AZ.BS.BH.BD.BB.BY'
+ '.BE.BZ.BJ.BM.BT.BO.BA.BW.BV.BR.IO.BN.BG.BF.MM.BI.KH.CM.CA.CV.KY'
+ '.CF.TD.CL.CN.CX.CC.CO.KM.CG.CD.CK.CR.CI.HR.CU.CY.CZ.DK.DJ.DM.DO'
+ '.TP.EC.EG.SV.GQ.ER.EE.ET.FK.FO.FJ.FI.CS.SU.FR.FX.GF.PF.TF.GA.GM.GE.DE'
+ '.GH.GI.GB.GR.GL.GD.GP.GU.GT.GN.GW.GY.HT.HM.HN.HK.HU.IS.IN.ID.IR.IQ'
+ '.IE.IL.IT.JM.JP.JO.KZ.KE.KI.KW.KG.LA.LV.LB.LS.LR.LY.LI.LT.LU.MO.MK.MG'
+ '.MW.MY.MV.ML.MT.MH.MQ.MR.MU.YT.MX.FM.MD.MC.MN.MS.MA.MZ.NA'
+ '.NR.NP.NL.AN.NT.NC.NZ.NI.NE.NG.NU.NF.KP.MP.NO.OM.PK.PW.PA.PG.PY'
+ '.PE.PH.PN.PL.PT.PR.QA.RE.RO.RU.RW.GS.SH.KN.LC.PM.ST.VC.SM.SA.SN.SC'
+ '.SL.SG.SK.SI.SB.SO.ZA.KR.ES.LK.SD.SR.SJ.SZ.SE.CH.SY.TJ.TW.TZ.TH.TG.TK'
+ '.TO.TT.TN.TR.TM.TC.TV.UG.UA.AE.UK.US.UY.UM.UZ.VU.VA.VE.VN.VG.VI'
+ '.WF.WS.EH.YE.YU.ZR.ZM.ZW'


Set @Pos = @Long + 1 - CharIndex('.',Reverse(@Email)) -- localiza el último punto (locate last period)

If CharIndex(substring(@Email, @Pos,60), @Ext) > 0 -- Chequea terminación válida
Begin
Set @I = 1
While @I <= @Long
Begin
If CharIndex(Substring(@Email,@I,1),@Letras) = 0 -- Letra o caracter inválido (invalid char)
Break

Set @I = @I + 1
End
End

If @Long < @I
Set @Bueno = 1

End

Return @Bueno





Post #831492
Posted Wednesday, December 9, 2009 8:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 341, Visits: 688
Very nice! Thanks for all of the helpful tips and tricks. :)
Post #831497
Posted Wednesday, June 2, 2010 1:25 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 7:04 PM
Points: 44, Visits: 132
Dennis,
I like your pattern matching approach. However, I was concerned about the 128 extended characters, so I suggest the last condition be modified to changed the ~ to a ÿ, like this:

OR @vParameter LIKE '%[{-ÿ]%'


Sincerely,
Daniel
Post #931635
Posted Wednesday, June 2, 2010 1:52 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 7:04 PM
Points: 44, Visits: 132
Oops, ÿ doesn't work

Sincerely,
Daniel
Post #931655
Posted Wednesday, June 2, 2010 4:01 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 7:04 PM
Points: 44, Visits: 132
OK, here's the fix for ÿ:

OR @vParameter COLLATE Latin1_General_BIN LIKE '%[{-ÿ]%'


Sincerely,
Daniel
Post #931732
Posted Tuesday, August 24, 2010 10:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:28 PM
Points: 39, Visits: 218
Thanks Sean! I believe this is a lot more inclusive than the edits I’d coded in my script.
Libby Montgomery
Post #974292
Posted Tuesday, August 24, 2010 5:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 341, Visits: 688
Thanks. Hope it helps.
Post #974533
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse