Menu
Ok, there are a million regexes out there for validating an email address, but how about some basic email validation that can be integrated into a TSQL query for Sql Server 2005?
I don't want to use a CLR procedure or function. Just straight TSQL.
Has anybody tackled this already?
893
Using the function like REGEXP_LIKE user can validate the email in MSSQL also. User can write different function in ms sql to validate mail like in oracle. AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0; The above function is used to validate the email address in Microsoft sql.
So, the email validation is done by using pattern matching which can be done by using LIKE operator in MSSQL. It checks for the fixed pattern set by the database designer in the data and displays the data that matches the fixed pattern.
TSQL Function to Validate Email Addresses Here is a function that returns 1 for valid email address format and 0 for invalid address format. Let's create a table, insert random correct and incorrect email address then execute the function to verify the email addresses. Finally, let's test our email function.
Very basic would be:
SELECT EmailAddress, CASE WHEN EmailAddress LIKE '%_@_%_.__%' AND EmailAddress NOT LIKE '%[any obviously invalid characters]%' THEN 'Could be' ELSE 'Nope' END Validates FROM Table This matches everything with an @ in the middle, preceded by at least one character, followed by at least two, a dot and at least two for the TLD.
You can write more LIKE patterns that do more specific things, but you will never be able to match everything that could be an e-mail address while not letting slip through things that are not. Even with regular expressions you have a hard time doing it right. Additionally, even matching according to the very letters of the RFC matches address constructs that will not be accepted/used by most emailing systems.
Doing this on the database level is maybe the wrong approach anyway, so a basic sanity check as indicated above may be the best you can get performance-wise, and doing it in an application will provide you with far greater flexibility.
106
Here's a sample function for this that's a little more detailed, I don't remember where I got this from (years ago), or if I modified it, otherwise I would include proper attribution:
CREATE FUNCTION [dbo].[fnAppEmailCheck](@email VARCHAR(255)) --Returns true if the string is a valid email address. RETURNS bit as BEGIN DECLARE @valid bit IF @email IS NOT NULL SET @email = LOWER(@email) SET @valid = 0 IF @email like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%' AND LEN(@email) = LEN(dbo.fnAppStripNonEmail(@email)) AND @email NOT like '%@%@%' AND CHARINDEX('.@',@email) = 0 AND CHARINDEX('..',@email) = 0 AND CHARINDEX(',',@email) = 0 AND RIGHT(@email,1) between 'a' AND 'z' SET @valid=1 RETURN @valid END If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
