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