patindex experts unite ! - 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 2000 Forums

SQL Server Development (2000)

patindex experts unite !

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-06 : 14:35:15

I'm posting a function I wrote a year ago which validates an email string syntax.

If anyone wants to try their hand at a slicker solution, I'd like to see how it could be improved.

Sam

CREATE FUNCTION dbo.EmailIsValid (@Email varchar (100))
RETURNS BIT
AS
BEGIN
DECLARE @atpos int, @dotpos int

SET @Email = LTRIM(RTRIM(@Email)) -- remove leading and trailing blanks

IF LEN(@Email) = 0 RETURN(0) -- nothing to validate

SET @atpos = charindex('@',@Email) -- position of first (hopefully only) @

IF @atpos <= 1 OR @atpos = LEN(@Email) RETURN(0) -- @ is neither 1st or last or missing

IF CHARINDEX('@', @email, @atpos+1) > 0 RETURN(0) -- Two @s are illegal

IF CHARINDEX(' ',@Email) > 0 RETURN(0) -- Embedded blanks are illegal

SET @dotpos =  CHARINDEX('.',REVERSE(@Email)) -- location (from rear) of last dot

IF (@dotpos < 3) or (@dotpos > 4) or (LEN(@Email) - @dotpos) < @atpos RETURN (0) -- dot / 2 or 3 char, after @

RETURN(1) -- Whew !!

END
Go

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-06 : 14:44:11

I used regular expressions on the client side to validate my e-mail addresses.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-06 : 14:53:06

Yes. Client side is often more appropriate for validations like this. However, this function makes it handy to build a proc that returns all records with invalid email address.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources