Valid Email in Query

create function [dbo].[fn_isValidEmail](@string varchar(500))
returns int
as
begin
return (
 select
   case when @string LIKE '%_@_%_.__%'
     --AND @test NOT LIKE '%[]%'
   then 1
   else 0
   end
  ) 
end

Flatten Tree to Array

ITree is a partial class for EF that just has Parent and Children props on it

private List FlattenTree(List items,List outputItems) where T:ITree
{

 foreach(var item in items){
 
 outputItems.Add(item);
 
 if(item.Children.Count > 0){
  return FlattenTree(item.Children, outputItems);
 }
 
 }

 return outputItems;
}

Strip Non Alpha in Query

create function [dbo].[fn_stripNonAlpha](@temp varchar(1000))
returns varchar(1000)
as
begin
 while patIndex('%[^a-z]%', @temp) > 0
  set @temp = stuff(@temp, patIndex('%[^a-z]%', @temp), 1, '')

 return @temp
end

Strip Non Numerics in Query

create function[dbo].[fn_StripNonNumeric]( @string varchar(max))
returns varchar(max)
as
begin
if patindex('%[^0-9]%', @string) > 0
while patindex('%[^0-9]%', @string) > 0
set @string = stuff(@string, patindex('%[^0-9]%', @string), 1, '')
return @string
end

Replace Vanity Phone Numbers with Numbers

create function replacePhoneNumberVanityChars(@string varchar(max))
returns varchar(max)
begin

 declare @len as int = len(@string);
 declare @i as int = 1;
 declare @char as varchar(1) = null;

 while(@i<=@len)
 begin

  set @char = right(left(@string,@i),1);

  if(@char in ('A','B','C'))
   set @char = '2';
  if(@char in ('D','E','F'))
   set @char = '3';
  if(@char in ('G','H','I'))
   set @char = '4';
  if(@char in ('J','K','L'))
   set @char = '5';
  if(@char in ('M','N','O'))
   set @char = '6';
  if(@char in ('P','Q','R','S'))
   set @char = '7';
  if(@char in ('T','U','V'))
   set @char = '8'; 
  if(@char in ('W','X','Y','Z'))
   set @char = '9';
  
  set @string = left(@string,@i-1) + @char  + right(@string,len(@string)-@i);

  set @char = null;
  set @i = @i +1;

 end
 
 return @string;

end

Database Lookup on Route in .NET MVC4

I started messing around in .NET MVC4's Routing and was wondering if you had a custom route where you need to look up something in the database, how would that work?

I found out this was possible using the constraints attribute when you setup your route. I created a new constraint by extending IRouteConstraint and forwarded the look up on to a model where I did the database look up.

App_Start >> RouteConfig

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
using myApp.App_Start;

namespace myApp
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "LocationSite",
                url: "{id}/{action}",
                defaults: new { controller = "Snowboard", action = "Index", id = UrlParameter.Optional },
                constraints: new { id = new SnowboardSiteRouteConstraint()  }/*---database lookup for snowboards---*/
            );
         }
    }
}

App_Start >> SnowboardSiteRouteConstraint

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Routing;
using myApp.Models;

namespace myApp.App_Start
{
    public class SnowboardSiteRouteConstraint: IRouteConstraint
    {
        public bool Match(HttpContextBase httpContext, Route route, string parameterName,RouteValueDictionary values, RouteDirection routeDirection)
        {
           /*---database lookup for snowboards---*/

            return SnowboardSite.IsSnowboardSiteName((string)values["id"]);

        }
    }
}

Model >> SnowboardSite

using System;
using System.Collections.Generic;
using System.Web;
using myApp.Persistence;
using myApp.Entities;
using NHibernate;
using NHibernate.Linq;

namespace myApp.Models{
    public class SnowboardSite{
        public static bool IsSnowboardSiteName(string boardName){

            /*---database lookup for snowboards, return the result as boolean---*/

            return [[result of database lookup goes here as a boolean]];
        }
    }
}

SQL Multiple table constraint

I've always wanted to know how to do a constraint across tables and someone on StackOverflow did too apparently. Here's the link:

How do I create a multiple-table check constraint?t