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")
);
}
}
}