Skip to main content

SQL CLR validation functions, including email address and phone number validation.

using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

namespace SqlClrCommon
{
    public partial class SqlFunctions
    {
        /// <summary>
        /// Validates an Email Address.
        /// </summary>
        /// <param name="emailAddress">The email address.</param>
        /// <returns>True if the specified string is valid email address,
        /// other wise false.</returns>
        /// <remarks>
        /// Will match only syntactically correct Email Addresses.
        /// Dose not validate an IPv6 Domain part.
        /// Validation rules and samples taken from here:
        /// http://en.wikipedia.org/wiki/Email_address#Invalid_email_addresses
        /// </remarks>
        [SqlFunction(
            Name = "IsEmailAddress",
            DataAccess = DataAccessKind.None,
            SystemDataAccess = SystemDataAccessKind.None,
            IsDeterministic = true)]
        public static SqlBoolean IsEmailAddress(
            [SqlFacet(MaxSize = 255)] SqlString emailAddress)
        {
            return new SqlBoolean(!emailAddress.IsNull &&
                Regex.IsMatch(emailAddress.Value, @"^(?:(?:[\w`~!#$%^&*\-=+;:{}'|,?\/]+(?:(?:\.(?:""(?:\\?[\w`~!#$%^&*\-=+;:{}'|,?\/\.()<>\[\] @]|\\""|\\\\)*""|[\w`~!#$%^&*\-=+;:{}'|,?\/]+))*\.[\w`~!#$%^&*\-=+;:{}'|,?\/]+)?)|(?:""(?:\\?[\w`~!#$%^&*\-=+;:{}'|,?\/\.()<>\[\] @]|\\""|\\\\)+""))@(?:[a-zA-Z\d\-]+(?:\.[a-zA-Z\d\-]+)*|\[\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\])$")
            );
        }

        /// <summary>
        /// Validates a Phone Number (North America).
        /// </summary>
        /// <param name="phoneNumber">The phone number.</param>
        /// <returns>True if the specified string is valid phone number,
        /// other wise false.</returns>
        /// <remarks>
        /// Matches 3334445555, 333.444.5555, 333-444-5555, 333 444 5555,
        /// (333) 444 5555 and all combinations thereof.
        /// Does not match international notation +13334445555,
        /// but matches domestic part in +1 333 4445555.
        /// </remarks>
        [SqlFunction(
            Name = "IsPhoneNumber",
            DataAccess = DataAccessKind.None,
            SystemDataAccess = SystemDataAccessKind.None,
            IsDeterministic = true)]
        public static SqlBoolean IsPhoneNumber(
            [SqlFacet(MaxSize = 255)] SqlString phoneNumber)
        {
            return new SqlBoolean(!phoneNumber.IsNull &&
                Regex.IsMatch(phoneNumber.Value, @"\(?\b[2-9][0-9]{2}\)?[-. ]?[2-9][0-9]{2}[-. ]?[0-9]{4}\b")
            );
        }
    }
}