SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function A2ROMAN(@n int ) 
--Converts an arabic numeral to roman, as a string.
returns VARCHAR(20)
as
BEGIN
DECLARE @i int, @temp char(1), @s VARCHAR(20)
DECLARE @p1 char(4),@p2 char(4),@p3 char(4),@p4 char(4)
SET @s=STR(@n,4,0)
SET @p1=' '
SET @p2=' '
SET @p3=' '
SET @p4=' '
SET @i=LEN(@s)
WHILE (@i>0)
BEGIN
SET @temp=UPPER(SUBSTRING(@s,@i,1))
IF LEN(@s)-@i=0
	SET @p1=CASE UPPER(SUBSTRING(@s,@i,1))
	WHEN '1' THEN 'I'
	WHEN '2' THEN 'II'
	WHEN '3' THEN 'III'
	WHEN '4' THEN 'IV'
	WHEN '5' THEN 'V'
	WHEN '6' THEN 'VI'
	WHEN '7' THEN 'VII'
	WHEN '8' THEN 'VIII'
	WHEN '9' THEN 'IX'
	ELSE ' '
	END
IF LEN(@s)-@i=1
	SET @p2=CASE UPPER(SUBSTRING(@s,@i,1))
	WHEN '1' THEN 'X'
	WHEN '2' THEN 'XX'
	WHEN '3' THEN 'XXX'
	WHEN '4' THEN 'XL'
	WHEN '5' THEN 'L'
	WHEN '6' THEN 'LX'
	WHEN '7' THEN 'LXX'
	WHEN '8' THEN 'LXXX'
	WHEN '9' THEN 'XC'
ELSE ' '
	END
IF LEN(@s)-@i=2
	SET @p3=CASE UPPER(SUBSTRING(@s,@i,1))
	WHEN '1' THEN 'C'
	WHEN '2' THEN 'CC'
	WHEN '3' THEN 'CCC'
	WHEN '4' THEN 'CD'
	WHEN '5' THEN 'D'
	WHEN '6' THEN 'DC'
	WHEN '7' THEN 'DCC'
	WHEN '8' THEN 'DCCC'
	WHEN '9' THEN 'CM'
ELSE ' '
	END
IF LEN(@s)-@i=3
	SET @p4=CASE UPPER(SUBSTRING(@s,@i,1))
	WHEN '1' THEN 'M'
	WHEN '2' THEN 'MM'
	WHEN '3' THEN 'MMM'
	WHEN '4' THEN 'MMMM'
ELSE ' '
	END
SET @i=@i-1
END
SET @s= @p4+@p3+@p2+@p1
SET @s=REPLACE(@s,' ','')
RETURN @s
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ACOSEC(@a float ) 
--Returns the angle in radians whose cosecant is the given float expression (also called arccosecant).
returns float
as
BEGIN
return (ASIN(1/@a))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ACOSH(@a float ) 
--Returns the inverse hyperbolic cosine of a number
returns float
as
BEGIN
return LOG(@a+SQRT(@a*@a-1))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ACOT(@a float ) 
--Returns the angle in radians whose cotangent is the given float expression (also called arccotangent).
returns float
as
BEGIN
return (ATAN(1/@a))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ADD_MONTHS (@d datetime, @n int ) 
--Returns the date d plus i months
returns datetime
as
BEGIN
RETURN dateadd(m,@n,@d)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ARR(@n bigint, @k bigint) 
--Returns the number of arrangements for a given number of objects.
returns bigint
as
BEGIN
return dbo.FACT(@n)/(dbo.FACT(@n-@k))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ASCII2EBCDIC(@s VARCHAR(255) ) 
--Converts a string from ASCII to EBCDIC.
returns  VARCHAR(255)
as
BEGIN
DECLARE @i int, @temp char(1),@ebcdic char(1), @result VARCHAR(255) 
SET @i=1
SET @result=''
WHILE (@i<=LEN(@s))
BEGIN
SET @temp=SUBSTRING(@s,@i,1)
SET @ebcdic=CASE @temp
		WHEN char(13) THEN '%'
		WHEN ' ' THEN '@'
		WHEN '.' THEN 'K'
		WHEN '<' THEN 'L'
		WHEN '(' THEN 'M'
		WHEN '+' THEN 'N'
		WHEN '|' THEN 'O'
		WHEN '&' THEN 'P'
		WHEN '!' THEN 'Z'
		WHEN '$' THEN CHAR(91)
		WHEN ')' THEN CHAR(92)
		WHEN '*' THEN CHAR(93)
		WHEN ';' THEN CHAR(94)
		WHEN '-' THEN CHAR(96)
		WHEN '`' THEN CHAR(185)
		WHEN '/' THEN 'a'
		WHEN ',' THEN 'k'
		WHEN '%' THEN 'l'
		WHEN '_' THEN 'm'
		WHEN '>' THEN 'n'
		WHEN '?' THEN 'o'
		WHEN '' THEN 'p'
		WHEN ':' THEN 'z'
		WHEN '#' THEN CHAR(123)
		WHEN '@' THEN CHAR(124)
		WHEN '''' THEN CHAR(125)
		WHEN '=' THEN CHAR(126)
		WHEN '"' THEN CHAR(127)
		ELSE ''
		END
IF @ebcdic=''
SET @ebcdic=CASE
	WHEN ASCII(@temp) BETWEEN 97 AND 105 THEN CHAR(ASCII(@temp)+32) 
	WHEN ASCII(@temp) BETWEEN 106 AND 114 THEN CHAR(ASCII(@temp)+39) 
	WHEN ASCII(@temp) BETWEEN 115 AND 122 THEN CHAR(ASCII(@temp)+47) 
	WHEN ASCII(@temp) BETWEEN 65 AND 73 THEN CHAR(ASCII(@temp)+128) 
	WHEN ASCII(@temp) BETWEEN 74 AND 82 THEN CHAR(ASCII(@temp)+135)
	WHEN ASCII(@temp) BETWEEN 83 AND 90 THEN CHAR(ASCII(@temp)+143)
	WHEN ASCII(@temp) BETWEEN 48 AND 57 THEN CHAR(ASCII(@temp)+192)
	ELSE ''
	END
SET @result=@result+@ebcdic
SET @i=@i+1
END
RETURN   @result
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ASEC(@a float ) 
--Returns the angle in radians whose secant is the given float expression (also called arcsecant).
returns float
as
BEGIN
return (ACOS(1/@a))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ASINH(@a float ) 
--Returns the inverse hyperbolic sine of a number.
returns float
as
BEGIN
return LOG(@a+SQRT(@a*@a+1))
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ATANH(@a float ) 
--Returns the inverse hyperbolic tangent of a number.
returns float
as
BEGIN
return LOG((1+@a)/(1-@a))/2
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function BINTODEC(@s VARCHAR(255) ) 
--Converts a binary number to decimal.
returns int
as
BEGIN
DECLARE @i int, @temp char(1), @result int
SELECT @i=1
SELECT @result=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
SELECT @result=@result+ (ASCII(@temp)-48)*POWER(2,LEN(@s)-@i)
SELECT @i=@i+1
END
return @result
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function CHARINDEXREV(@s varchar(255),@p varchar(255) ) 
--Returns the position of an occurrence of one string within another, from the end of string.
returns int
as
BEGIN
DECLARE @i int
SET @i=1
WHILE charindex(@s, @p, @i)>0
BEGIN
SET @i=charindex(@s, @p, @i)+1
END
IF @i>0
	SET @i=@i-1
RETURN  @i
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function COMBIN(@n bigint, @k bigint) 
--Returns the number of combinations for a given number of objects.
returns bigint
as
BEGIN
return dbo.FACT(@n)/(dbo.FACT(@k)*dbo.FACT(@n-@k))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function COMPLEMENT1(@a int ) 
--Returns a number's one's complement.
returns int
as
BEGIN
return ~@a
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function COMPLEMENT2(@a int ) 
--Returns a number's two's complement.
returns int
as
BEGIN
return (~@a+1)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function COSEC(@a float ) 
--Returns the trigonometric cosecant of the given angle (in radians) in the given expression.
returns float
as
BEGIN
return (1/SIN(@a))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function COSECH(@a float ) 
--Returns the hyperbolic cosecant of a number.
returns float
as
BEGIN
return 2/( POWER(dbo.E(),@a) -  POWER(dbo.E(),-@a) )
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function COSH(@a float ) 
--Returns the hyperbolic cosine of a number.
returns float
as
BEGIN
return ( POWER(dbo.E(),@a) +  POWER(dbo.E(),-@a) )/2
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function COTH(@a float ) 
--Returns the hyperbolic cotangent of a number.
returns float
as
BEGIN
return (dbo.COSH(@a)/dbo.SINH(@a))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function CRYPTX8( @s VARCHAR(1024), @k VARCHAR(8) ) 
--Returns a string s1 encrypted/decrypted with key s2, up to 8 chars ( XOR encryption ). 
returns VARCHAR(1024)
as
BEGIN
DECLARE @result VARCHAR(1024), @l int, @i int, @j int, @temp tinyint, @x tinyint
SET @i=LEN(@k)
IF @i<8--if the pwd<8 char
	BEGIN
	SET @k=@k+@k+@k+@k+@k+@k+@k+@k--add pwd to itself
	SET @k=LEFT(@k,8)
	END
SET @l=(LEN(@s) % 8)
IF @l<>0--if there are no complete 64 bit blocks
	BEGIN
	SET @i=(LEN(@s))/8+1
	SET @l= @i*8-len(@s)
	SET @s=@s+replicate('*',@l)
	END
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
	BEGIN
	SET @j=0
	WHILE @j<8
		BEGIN	
		SET @temp=ASCII(SUBSTRING(@s,@i+@j,1))
		SET @x=ASCII(SUBSTRING(@k,@j+1,1))
		SET @result=@result + CHAR(@temp ^ @x)	
		SET @j=@j+1
		END
	SET @i=@i+8
	END
IF @l<>0
	BEGIN	
	SET @result=LEFT(@result,LEN(@result)-@l)
	END
RETURN    @result
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function CUBE(@a float ) 
--Returns the cube of the given expression.
returns float
as
BEGIN
return @a*@a*@a
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function DDATE( @d as DATETIME) 
--Returns the date from a datetime input as a string.
returns varchar(255)
as
BEGIN
DECLARE @s varchar(255) 
SET @s= CONVERT(VARCHAR(255),@d,101)
RETURN @s
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function DEC(@a int ) 
--Returns a number decremented by 1.
returns int
as
BEGIN
return @a-1
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function DECTOBIN(@n int ) 
--Converts a decimal number to binary.
returns varchar(255)
as
BEGIN
DECLARE @i int,@temp int, @s varchar(255)
SET @i=@n
SET @s=''
WHILE (@i>0)
BEGIN
SET @temp=@i % 2
SET @i=@i /2
SET @s=char(48+@temp)+@s
END
RETURN @s
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function DECTOHEX(@n int ) 
--Converts a decimal number to hexadecimal.
returns varchar(255)
as
BEGIN
DECLARE @i int,@temp int, @s varchar(255)
SET @i=@n
SET @s=''
WHILE (@i>0)
BEGIN
SET @temp=@i % 16
SET @i=@i /16
IF @temp>9
	SET @s=char(55+@temp)+@s
ELSE
	SET @s=char(48+@temp)+@s
END
RETURN @s
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function DECTON(@n int, @b int ) 
--Converts a decimal number to base n.
returns varchar(255)
as
BEGIN
DECLARE @i int,@temp int, @s varchar(255)
SET @i=@n
SET @s=''
WHILE (@i>0)
BEGIN
SET @temp=@i % @b
SET @i=@i /@b
IF @temp>9
	SET @s=char(55+@temp)+@s
ELSE
	SET @s=char(48+@temp)+@s
END
RETURN @s
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function DECTOOCT(@n int ) 
--Converts a decimal number to octal.
returns varchar(255)
as
BEGIN
DECLARE @i int,@temp int, @s varchar(255)
SET @i=@n
SET @s=''
WHILE (@i>0)
BEGIN
SET @temp=@i % 8
SET @i=@i /8
SET @s=char(48+@temp)+@s
END
RETURN @s
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function DEG2GRAD(@a float ) 
--Converts an angle from degrees to grads.
returns float
as
BEGIN
return (@a*10.0/9.0)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function DISTANCE(@x1 float,@y1 float, @x2 float,@y2 float) 
--Returns the distance between 2 points P(f1, f2) to T(f3, f4). 
returns float
as
BEGIN
return sqrt((@x1-@x2)*(@x1-@x2)+(@y1-@y2)*(@y1-@y2))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function DIVI(@a bigint, @b bigint, @precision bigint) 
--Returns the result of the division of i1 by i2 with precision i3 (Infinite precision division).  
returns VARCHAR(5000)
as
BEGIN
DECLARE @l bigint, @p bigint,@d bigint,@t bigint,@result VARCHAR(5000), @err bit
SET @result=''
SET @l=10
SET @err=0
SET @t=@a/@b
WHILE @err=0
	BEGIN
	SET @a=@a*@l
	IF @b>@a
		SET @result=@result+'0'
	WHILE (@b > @a)
		BEGIN
		SET @a=@a*@l
		IF @b>@a
			SET @result=@result+'0'
		END
	SET @p=@a/@b
	IF (@p * @b) < @a 
		SET @p = @p + 1
	SET @d = @p * @b
	IF @d=@a
		BEGIN
		SET @err=1
		SET @result=@result+CONVERT(VARCHAR(20),(@p))
		END
	IF @d>@a
		BEGIN
		SET @p=@p-1
		SET @result=@result+CONVERT(VARCHAR(20),(@p))
		IF LEN(@result)>@precision
			SET @err=1
		SET @a = @a - @p * @b
		END
	END
SET @l=LEN(CONVERT(VARCHAR(20),(@t)))
IF @p=0
	SET @result='0.'+@result
ELSE
	SET @result=LEFT(@result,@l)+'.'+RIGHT(@result,LEN(@result)-@l)
RETURN  @result
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function DTIME( @d as DATETIME) 
--Returns the time from a datetime input as a string.
returns varchar(255)
as
BEGIN
DECLARE @s varchar(255) 
SET @s= CONVERT(VARCHAR(255),@d,108)
RETURN @s
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function E( ) 
--Returns e, Natural Logarithmic Base.
returns float
as
BEGIN
return EXP(1)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function EBCDIC2ASCII(@s VARCHAR(255) ) 
--Converts a string from EBCDIC to ASCII.
returns  VARCHAR(255)
as
BEGIN
DECLARE @i int, @temp char(1),@ebcdic char(1), @result VARCHAR(255) 
SET @i=1
SET @result=''
WHILE (@i<=LEN(@s))
BEGIN
SET @temp=SUBSTRING(@s,@i,1)
SET @ebcdic=CASE @temp
		WHEN '%' THEN char(13)
		WHEN '@' THEN ' '
		WHEN 'K' THEN '.'
		WHEN 'L' THEN '<'
		WHEN 'M' THEN '('
		WHEN 'N' THEN '+'
		WHEN 'O' THEN '|'
		WHEN 'P' THEN '&'
		WHEN 'Z' THEN '!'
		WHEN CHAR(91) THEN '$'
		WHEN CHAR(92) THEN ')'
		WHEN CHAR(93) THEN '*'
		WHEN CHAR(94) THEN ';'
		WHEN CHAR(96) THEN '-'
		WHEN CHAR(185) THEN '`'
		WHEN 'a' THEN '/'
		WHEN 'k' THEN ','
		WHEN 'l' THEN '%'
		WHEN 'm' THEN '_'
		WHEN 'n' THEN '>'
		WHEN 'o' THEN '?'
		WHEN 'p' THEN ''
		WHEN 'z' THEN ':'
		WHEN CHAR(123) THEN '#'
		WHEN CHAR(124) THEN '@'
		WHEN CHAR(125) THEN ''''
		WHEN CHAR(126) THEN '='
		WHEN CHAR(127) THEN '"'
		ELSE ''
		END
IF @ebcdic=''
	SET @ebcdic=CASE
			WHEN ASCII(@temp) BETWEEN 129 AND 137 THEN CHAR(ASCII(@temp)-32) 
			WHEN ASCII(@temp) BETWEEN 145 AND 153 THEN CHAR(ASCII(@temp)-39) 
			WHEN ASCII(@temp) BETWEEN 162 AND 169 THEN CHAR(ASCII(@temp)-47) 
			WHEN ASCII(@temp) BETWEEN 193 AND 201 THEN CHAR(ASCII(@temp)-128) 
			WHEN ASCII(@temp) BETWEEN 209 AND 217 THEN CHAR(ASCII(@temp)-135)
			WHEN ASCII(@temp) BETWEEN 226 AND 233 THEN CHAR(ASCII(@temp)-143)
			WHEN ASCII(@temp) BETWEEN 240 AND 249 THEN CHAR(ASCII(@temp)-192)
			ELSE ''
			END
SET @result=@result+@ebcdic
SET @i=@i+1
END
RETURN @result
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function EQUIVALENT(@a int, @b int ) 
--Returns the result of a logical formal equivalence.
returns int
as
BEGIN
return ~(@a ^ @b)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function FACT(@n bigint) 
--Returns the factorial of a number.
returns bigint
as
BEGIN
    declare @temp bigint
    if (@n <= 1) 
	select @temp = 1
    else 
        select @temp = @n * dbo.FACT(@n - 1)
    return @temp
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function FACTDOUBLE(@n float ) 
--Returns the double factorial of a number.
returns float 
as
BEGIN
    declare @temp float 
    if (@n <= 1) 
	select @temp = 1
    else 
        select @temp = @n * dbo.FACTDOUBLE(@n - 1)
    return @temp
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function FIBONACCI(@n bigint) 
--Returns the Fibonacci series for a given number.
returns bigint
as
BEGIN
    declare @temp bigint
    if (@n <=2) 
	select @temp = 1
    else 
        select @temp =  dbo.FACT(@n - 1)+ dbo.FACT(@n - 2)
    return @temp
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function FRAC(@a float ) 
--Returns the decimal part of a number.
returns float
as
BEGIN
return (@a-convert(int,@a))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function FROMMORSE(@s varchar(255) ) 
--Returns the text corresponding to a morse code string.
returns  varchar(255)
as
BEGIN
DECLARE @i int,@j int,@p int, @result varchar(255),@chars1 char(26),@chars2 char(10)
DECLARE @chars3 char(3), @morse1 char(104)
DECLARE @morse2 char(50),@morse3 char(18), @temp varchar(6)
SET @chars1='ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SET @chars2='0123456789'
SET @chars3='.,?'
SET @morse1='.-  -...-.-.-.. .   ..-.--. ......  .----.- .-..--  -.  --- .--.--.-.-. ... -   ..- ...-.-- -..--.----..'
SET @morse2='-----.----..---...--....-.....-....--...---..----.'
SET @morse3='.-.-.---..--..--..'
SET @result=''
SET @s=LTRIM(RTRIM(@s))
WHILE CHARINDEX('  ',@s)>0
	SET @s=REPLACE(@s,'  ',' ')
SET @s=@s+' '
SET @i=1
SET @j=CHARINDEX(' ',@s,@i)-1
WHILE (@j>0)
BEGIN
SET @temp=(SUBSTRING(@s,@i,@j-@i+1))
IF LEN(@temp)<5
	BEGIN
	SET @p=1
	WHILE @p<(104)
		BEGIN
		IF @temp=LTRIM(RTRIM(SUBSTRING(@morse1,@p,4)))
			SET @result=@result+SUBSTRING(@chars1,@p/4+1,1)
		SET @p=@p+4
		END
	END 
IF LEN(@temp)=5
	BEGIN
	SET @p=1
	WHILE @p<(50)
		BEGIN
		IF @temp=LTRIM(RTRIM(SUBSTRING(@morse2,@p,5)))
			SET @result=@result+SUBSTRING(@chars2,@p/5+1,1)
		SET @p=@p+5
		END
	END 
IF LEN(@temp)=6
	BEGIN
	SET @p=1
	WHILE @p<(18)
		BEGIN
		IF @temp=LTRIM(RTRIM(SUBSTRING(@morse3,@p,6)))
			SET @result=@result+SUBSTRING(@chars3,@p/6+1,1)
		SET @p=@p+6
		END
	END 
SET @i=@j+2
SET @j=CHARINDEX(' ',@s,@i)-1
END
RETURN @result
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function GCD(@a int, @b int) 
--Returns the greatest common divisor of 2 numbers.
returns int
as
BEGIN
declare @c int
select @c=1
While (@c <> 0)
	BEGIN        
	select @c=@a % @b
	select @a=@b
	select @b=@c
        END
return @a
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function GETBIT(@a int, @b int ) 
--Returns the value of a certain bit.
returns int
as
BEGIN
return ABS(SIGN(@a & (POWER(2,@b))))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function GRAD2DEG(@a float ) 
--Converts an angle from grads to degrees.
returns float
as
BEGIN
return (@a*9.0/10.0)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function GRAD2RAD(@a float ) 
--Converts an angle from grads to radians.
returns float
as
BEGIN
return (@a*200.0/PI())
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function GREGORIAN2HIJRI(@d datetime) 
--Returns the date FROM Gregorian into Hijri calendar
returns NVARCHAR(100)
as
BEGIN
return CONVERT(NVARCHAR(100), @d, 131)
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function HEXTODEC(@s VARCHAR(255) ) 
--Converts an hexadecimal number to decimal.
returns int
as
BEGIN
DECLARE @i int, @temp char(1), @result int
SELECT @i=1
SELECT @result=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=UPPER(SUBSTRING(@s,@i,1))
IF (@temp>='0') AND (@temp<='9') 
	SELECT @result=@result+ (ASCII(@temp)-48)*POWER(16,LEN(@s)-@i)
ELSE
	IF (@temp>='A') AND (@temp<='F') 
		SELECT @result=@result+ (ASCII(@temp)-55)*POWER(16,LEN(@s)-@i)
SELECT @i=@i+1
END
return @result
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function HIJRI2GREGORIAN(@d NVARCHAR(100)) 
--Returns the date FROM Hijri into Gregorian calendar
returns datetime
as
BEGIN
return  CONVERT(datetime, @d, 131)
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function IIF(@b bit, @t SQL_VARIANT,@f SQL_VARIANT  ) 
--Returns one of two parts, depending on the evaluation of an expression.
returns SQL_VARIANT
as
BEGIN
DECLARE @temp SQL_VARIANT
IF @b=1
	SELECT @temp=@t
ELSE 
	SELECT @temp=@f
return @temp
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function IMPLIES(@a int, @b int ) 
--Returns the result of a logical formal implication.
returns int
as
BEGIN
return ~@a | @b
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function INC(@a int ) 
--Returns a number incremented by 1.
returns int
as
BEGIN
return @a+1
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function INCLUDED(@s varchar(255),@p varchar(255) ) 
--Returns how many times a string is included (occurs) into another one.
returns int
as
BEGIN
DECLARE @i int,@c int
SET @i=1
SET @c=0
WHILE charindex(@s, @p, @i)>0
BEGIN
SET @i=charindex(@s, @p, @i)+1
SET @c=@c+1
END
RETURN  @c
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function INITCAP (@s varchar(255) ) 
--Returns a string with the first letter of each word in uppercase, all other letters in lowercase (capitalize first character).  
returns varchar(255)
as
BEGIN
DECLARE @i int, @c char(1),@result varchar(255)
SET @result=LOWER(@s)
SET @i=2
SET @result=STUFF(@result,1,1,UPPER(SUBSTRING(@s,1,1)))
WHILE @i<=LEN(@s)
	BEGIN
	SET @c=SUBSTRING(@s,@i,1)
	IF (@c=' ') OR (@c=';') OR (@c=':') OR (@c='!') OR (@c='?') OR (@c=',')OR (@c='.')OR (@c='_')
		IF @i<LEN(@s)
			BEGIN
			SET @i=@i+1
			SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@s,@i,1)))
			END
	SET @i=@i+1
	END
RETURN  @result
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function IPOCTECT(@s varchar(15), @o int) 
--Returns an octect i (1-4) from an IP.
returns varchar(3)
as
BEGIN
DECLARE @u VARCHAR(3), @v VARCHAR(3), @x VARCHAR(3),@y VARCHAR(3), @i int, @j int, @result varchar(15)
IF (dbo.INCLUDED('.',@s)<>3) OR (@i<1) OR (@i>4)
	BEGIN
	SET @result=''
	GOTO done
	END
SET @i=CHARINDEX('.',@s)
SET @u=LEFT(@s,@i-1)
SET @j=CHARINDEX('.',@s,@i+1)
SET @v=substring(@s,@i+1,@j-@i-1)
SET @i=CHARINDEX('.',@s,@j+1)
SET @x=substring(@s,@j+1,@i-@j-1)
SET @y=substring(@s,@i+1,LEN(@s)-@i)
IF ISNUMERIC(@u)=0 OR ISNUMERIC(@v)=0 OR ISNUMERIC(@x)=0 OR ISNUMERIC(@y)=0
	BEGIN
	SET @result=''
	GOTo done
	END
IF (CONVERT(INT, @u)<0) OR  (CONVERT(INT, @v)<0) OR  (CONVERT(INT, @x)<0)  OR  (CONVERT(INT, @y)<0) 
	BEGIN
	SET @result=''
	GOTo done
	END
IF (CONVERT(INT, @u)>255) OR  (CONVERT(INT, @v)>255) OR  (CONVERT(INT, @x)>255)  OR  (CONVERT(INT, @y)>255) 
	BEGIN
	SET @result=''
	GOTo done
	END
SET @result=CASE @o
	WHEN 1 THEN @u
	WHEN 2 THEN @v
	WHEN 3 THEN @x
	WHEN 4 THEN @y
	END
done:
RETURN  @result
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISABUNDNUM(@n bigint ) 
--Returns true if the number is abundant
returns bit
as
BEGIN
DECLARE @b bit
IF dbo.SUMALIQUOT(@n)>@n
	SET @b=1
ELSE
	SET @b=0
RETURN @b
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISALPHA(@s VARCHAR(50) ) 
--Returns true if the string has valid alphanumeric characters.
returns bit
as
BEGIN
DECLARE @i int, @temp char(1), @bool bit
SELECT @i=1
SELECT @bool=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
--PRINT @temp
if (@temp<='z') AND (@temp>='a') OR  (@temp<='Z') AND (@temp>='A') OR  (@temp<='9') AND (@temp>='0') OR (@temp='-')  OR (@temp='.') 
	SELECT @bool=1
SELECT @i=@i+1
END
return @bool
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISBIN(@s VARCHAR(50) ) 
--Returns true if the string is a valid binary number. 
returns bit
as
BEGIN
DECLARE @i int, @temp char(1), @bool bit
SELECT @i=1
SELECT @bool=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
--PRINT @temp
if  (@temp='0')  OR (@temp='1') 
	SELECT @bool=1
SELECT @i=@i+1
END
return @bool
END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISDEFNUM(@n bigint ) 
--Returns true if the number is deficient
returns bit
as
BEGIN
DECLARE @b bit
IF dbo.SUMALIQUOT(@n)<@n
	SET @b=1
ELSE
	SET @b=0
RETURN @b
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISEMPTY(@a SQL_VARIANT ) 
--Returns true if the input is empty.
returns BIT
as
BEGIN
DECLARE @temp bit
IF (@a='')
	SELECT @temp=1
ELSE
	SELECT @temp=0
return @temp
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISEVEN(@a int ) 
--Returns true if the number is even.
returns bit
as
BEGIN
return ~(CONVERT(bit, @a & 1 ))
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISHEX(@s VARCHAR(50) ) 
--Returns true if the string is a valid hexadecimalal number. 
returns bit
as
BEGIN
DECLARE @i int, @temp char(1), @bool bit
SELECT @i=1
SELECT @bool=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
if (@temp<='f') AND (@temp>='a') OR  (@temp<='F') AND (@temp>='A') OR  (@temp<='9') AND (@temp>='0')  
	SELECT @bool=1
SELECT @i=@i+1
END
return @bool
END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISINTNUMBER(@s VARCHAR(50) ) 
--Returns true if the string is a valid integer number.
returns bit
as
BEGIN
DECLARE @i int, @temp char(1), @bool bit
SELECT @i=1
SELECT @bool=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
if (@temp<='9') AND (@temp>='0') OR (@temp='-') 
	SELECT @bool=1
SELECT @i=@i+1
END
return @bool
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISINTPOSNUMBER(@s VARCHAR(50) ) 
--Returns true if the string is a valid positive integer number. 
returns bit
as
BEGIN
DECLARE @i int, @temp char(1), @bool bit
SELECT @i=1
SELECT @bool=1
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
--PRINT @temp
if (@temp>'9') OR (@temp<'0')
	SELECT @bool=0
SELECT @i=@i+1
END
return @bool
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISITNULL(@a SQL_VARIANT ) 
--Returns true if the input is null.
returns BIT
as
BEGIN
DECLARE @temp bit
IF (@a=NULL)
	SELECT @temp=1
ELSE
	SELECT @temp=0
return @temp
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISLETTER(@s VARCHAR(50) ) 
--Returns true if the string has only letters.
returns bit
as
BEGIN
DECLARE @i int, @temp char(1), @bool bit
SELECT @i=1
SELECT @bool=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
--PRINT @temp
if (@temp<='z') AND (@temp>='a') OR  (@temp<='Z') AND (@temp>='A') 
	SELECT @bool=1
SELECT @i=@i+1
END
return @bool
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISNEG(@a float ) 
--Returns true if the number is negative.
returns BIT
as
BEGIN
DECLARE @temp bit
IF (@a < 0)
	SELECT @temp=1
ELSE
	SELECT @temp=0
return @temp
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISNUMBER(@s VARCHAR(50) ) 
--Returns true if the string is a valid number. 
returns bit
as
BEGIN
DECLARE @i int, @temp char(1), @bool bit
SELECT @i=1
SELECT @bool=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
--PRINT @temp
if (@temp<='9') AND (@temp>='0') OR (@temp='-')  OR (@temp='.') 
	SELECT @bool=1
SELECT @i=@i+1
END
return @bool
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISOCT(@s VARCHAR(50) ) 
--Returns true if the string is a valid octal number.
returns bit
as
BEGIN
DECLARE @i int, @temp char(1), @bool bit
SELECT @i=1
SELECT @bool=1
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
--PRINT @temp
if (@temp>'7') OR (@temp<'0')
	SELECT @bool=0
SELECT @i=@i+1
END
return @bool
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISODD(@a int ) 
--Returns true if the number is odd.
returns bit
as
BEGIN
return CONVERT(bit, @a & 1 )
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISPERFNUM(@n bigint ) 
--Returns true if the number is perfect
returns bit
as
BEGIN
DECLARE @b bit
IF dbo.SUMALIQUOT(@n)=@n
	SET @b=1
ELSE
	SET @b=0
RETURN @b
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISPOSNUMBER(@s VARCHAR(50) ) 
--Returns true if the string is a valid positive number.
returns bit
as
BEGIN
DECLARE @i int, @temp char(1), @bool bit
SELECT @i=1
SELECT @bool=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
--PRINT @temp
if (@temp<='9') AND (@temp>='0')  OR (@temp='.') 
	SELECT @bool=1
SELECT @i=@i+1
END
return @bool
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISPRIME(@i INT ) 
--Returns true if the number is prime. 
returns bit
as
BEGIN
DECLARE @c int, @t int, @result bit
SET @result=1
IF (@i & 1)=0
	BEGIN
	SET @result=0
	GOTO done
	END
SET @c=3
SET @t=SQRT(@i)
WHILE @c<=@t
	BEGIN
	IF @i % @c=0
		BEGIN
		SET @result=0
		GOTO done
		END
	SET @c=@c+2
	END
done:
RETURN  @result
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ISROMAN(@s VARCHAR(255) ) 
--Returns true if the string is a valid Roman numeral.
returns bit
as
BEGIN
DECLARE @i int, @temp char(1), @bool bit
SELECT @i=1
SELECT @bool=1
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=UPPER(SUBSTRING(@s,@i,1))
--LOOK FOR INVALID CHARS
if  NOT( (@temp='I')  OR (@temp='V') OR (@temp='X') OR (@temp='L') OR (@temp='C') OR (@temp='D')  OR (@temp='M') )
	SELECT @bool=0
SELECT @i=@i+1
END
--LOOK FOR INVALID SEQUENCE SUCH AS IIII INSTEAD OF IV
IF (CHARINDEX('IIII',UPPER(@s))>0) OR (CHARINDEX('VV',UPPER(@s))>0) OR (CHARINDEX('XXXX',UPPER(@s))>0) OR (CHARINDEX('LL',UPPER(@s))>0) OR (CHARINDEX('CCCC',UPPER(@s))>0) OR (CHARINDEX('DD',UPPER(@s))>0) OR (CHARINDEX('MMMMM',UPPER(@s))>0)
	SELECT @bool=0
--LOOK FOR INVALID PRECEDENCE SUCH AS IL (49?) INSTEAD OF XLIX
IF (CHARINDEX('IL',UPPER(@s))>0) OR (CHARINDEX('IC',UPPER(@s))>0) OR (CHARINDEX('ID',UPPER(@s))>0) OR (CHARINDEX('IM',UPPER(@s))>0) OR (CHARINDEX('VX',UPPER(@s))>0) OR (CHARINDEX('VL',UPPER(@s))>0) OR (CHARINDEX('VC',UPPER(@s))>0) OR (CHARINDEX('VD',UPPER(@s))>0) OR (CHARINDEX('VM',UPPER(@s))>0) OR (CHARINDEX('XC',UPPER(@s))>0) OR (CHARINDEX('XD',UPPER(@s))>0) OR (CHARINDEX('XM',UPPER(@s))>0) OR (CHARINDEX('LC',UPPER(@s))>0) OR (CHARINDEX('LD',UPPER(@s))>0) OR (CHARINDEX('LM',UPPER(@s))>0) OR (CHARINDEX('CM',UPPER(@s))>0) OR (CHARINDEX('DM',UPPER(@s))>0)
	SELECT @bool=0
--LOOK FOR INVALID PRECEDENCE SUCH AS IIV INSTEAD OF III
IF (CHARINDEX('IIV',UPPER(@s))>0) OR (CHARINDEX('IIX',UPPER(@s))>0) OR (CHARINDEX('XXL',UPPER(@s))>0) OR (CHARINDEX('XXC',UPPER(@s))>0) OR (CHARINDEX('CCD',UPPER(@s))>0) OR (CHARINDEX('CCM',UPPER(@s))>0)
	SELECT @bool=0
return @bool
END









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function LAST_DAY(@d datetime ) 
returns datetime
as
BEGIN
DECLARE @nextmonth datetime, @i int
SET @nextmonth=dateadd(m,1,@d)
SET @i=-day(@nextmonth)
SET @nextmonth=dateadd(d,@i,@nextmonth)
return day(@nextmonth) 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function LCM(@a int, @b int ) 
--Returns the least common multiple of 2 numbers.
returns int
as
BEGIN
return (@a * @b) / dbo.GCD(@a, @b)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function LEVENSHTEIN( @s varchar(50), @t varchar(50) ) 
--Returns the Levenshtein Distance between strings s1 and s2.
--Original developer: Michael Gilleland    http://www.merriampark.com/ld.htm
--Translated to TSQL by Joseph Gama
returns varchar(50)
as
BEGIN
DECLARE @d varchar(2500), @LD int, @m int, @n int, @i int, @j int,
@s_i char(1), @t_j char(1),@cost int
--Step 1
SET @n=LEN(@s)
SET @m=LEN(@t)
SET @d=replicate(CHAR(0),2500)
If @n = 0
	BEGIN
	SET @LD = @m
	GOTO done
	END
If @m = 0
	BEGIN
	SET @LD = @n
	GOTO done
	END
--Step 2
SET @i=0
WHILE @i<=@n
	BEGIN
	SET @d=STUFF(@d,@i+1,1,CHAR(@i))--d(i, 0) = i
	SET @i=@i+1
	END

SET @i=0
WHILE @i<=@m
	BEGIN
	SET @d=STUFF(@d,@i*(@n+1)+1,1,CHAR(@i))--d(0, j) = j
	SET @i=@i+1
	END
--goto done
--Step 3
	SET @i=1
	WHILE @i<=@n
		BEGIN
		SET @s_i=(substring(@s,@i,1))
--Step 4
	SET @j=1
	WHILE @j<=@m
		BEGIN
		SET @t_j=(substring(@t,@j,1))
		--Step 5
		If @s_i = @t_j
			SET @cost=0
		ELSE
			SET @cost=1
--Step 6
		SET @d=STUFF(@d,@j*(@n+1)+@i+1,1,CHAR(dbo.MIN3(
		ASCII(substring(@d,@j*(@n+1)+@i-1+1,1))+1,
		ASCII(substring(@d,(@j-1)*(@n+1)+@i+1,1))+1,
		ASCII(substring(@d,(@j-1)*(@n+1)+@i-1+1,1))+@cost)
		))
		SET @j=@j+1
		END
	SET @i=@i+1
	END      
--Step 7
SET @LD = ASCII(substring(@d,@n*(@m+1)+@m+1,1))
done:
--RETURN @LD
--I kept this code that can be used to display the matrix with all calculated values
--From Query Analyser it provides a nice way to check the algorithm in action
--
RETURN @LD
--declare @z varchar(8000)
--set @z=''
--SET @i=0
--WHILE @i<=@n
--	BEGIN
--	SET @j=0
--	WHILE @j<=@m
--		BEGIN
--		set @z=@z+CONVERT(char(3),ASCII(substring(@d,@i*(@m+1 )+@j+1 ,1)))
--		SET @j=@j+1 
--		END
--	SET @i=@i+1
--	END
--print dbo.wrap(@z,3*(@n+1))
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function LOG2( @n float) 
--Returns the logarithm (base 2) of the given float expression.
returns float
as
BEGIN
    return LOG(@n)/LOG(2)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function LOGN(@b float, @n float) 
--Returns the logarithm (base b) of the given float expression.
returns float
as
BEGIN
    return LOG(@n)/LOG(@b)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function LPAD(@s varchar(255), @n int, @p varchar(255) ) 
--Returns a string s1 left-padded to length i with a sequence of characters s2. 
returns varchar(255)
as
BEGIN
return REPLICATE(@p,@n)+@s
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function MAX2(@a int,@b int ) 
--Returns the largest of 2 numbers.
returns int
as
BEGIN
declare @temp int
if (@a > @b) 
	select @temp=@a
else 
	select @temp=@b
return @temp
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function MAX3(@a int,@b int,@c int ) 
--Returns the largest of 3 numbers.
returns int
as
BEGIN
declare @temp int
if (@a > @b)  AND (@a > @c)
	select @temp=@a
else 
	if (@b > @a)  AND (@b > @c)
		select @temp=@b
	else
		select @temp=@c
return @temp
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function MIN2(@a int,@b int ) 
--Returns the smallest of 2 numbers.
returns int
as
BEGIN
declare @temp int
if (@a < @b) 
	select @temp=@a
else 
	select @temp=@b
return @temp
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function MIN3(@a int,@b int,@c int ) 
--Returns the smallest of 3 numbers.
returns int
as
BEGIN
declare @temp int
if (@a < @b)  AND (@a < @c)
	select @temp=@a
else 
	if (@b < @a)  AND (@b < @c)
		select @temp=@b
	else
		select @temp=@c
return @temp
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function MONTHS_BETWEEN ( @d datetime, @e datetime ) 
--Returns number of months between dates d1 and d2. 
returns int
as
BEGIN
return datediff(m, @d, @e)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function MORSE(@s varchar(255) ) 
--Returns the morse code corresponding to a string.
returns  varchar(255)
as
BEGIN
DECLARE @i int,@result varchar(255),@chars1 char(26),@chars2 char(10)
DECLARE @chars3 char(3), @morse1 char(104)
DECLARE @morse2 char(50),@morse3 char(18), @temp char(1)
SET @chars1='ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SET @chars2='0123456789'
SET @chars3='.,?'
SET @morse1='.-  -...-.-.-.. .   ..-.--. ......  .----.- .-..--  -.  --- .--.--.-.-. ... -   ..- ...-.-- -..--.----..'
SET @morse2='-----.----..---...--....-.....-....--...---..----.'
SET @morse3='.-.-.---..--..--..'
SET @result=''
SET @i=1
WHILE (@i<=LEN(@s))
BEGIN
SET @temp=UPPER(SUBSTRING(@s,@i,1))
IF CHARINDEX(@temp,@chars1)>0
	SET @result=@result+' '+SUBSTRING(@morse1,CHARINDEX(@temp,@chars1)*4-3,4)
IF CHARINDEX(@temp,@chars2)>0
	SET @result=@result+' '+SUBSTRING(@morse2,CHARINDEX(@temp,@chars2)*5-4,5)
IF CHARINDEX(@temp,@chars3)>0
	SET @result=@result+' '+SUBSTRING(@morse3,CHARINDEX(@temp,@chars3)*6-5,6)
SET @i=@i+1
END
WHILE CHARINDEX('  ',@result)>0
	SET @result=REPLACE(@result,'  ',' ')
RETURN @result
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function NAND(@a int, @b int ) 
--Returns the result of a logical negated AND.
returns int
as
BEGIN
return ~(@a & @b)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function NEXT_DAY( @d datetime, @n int ) 
returns datetime
as
BEGIN
declare @i int, @result datetime
IF (@n<1)OR (@n>7)
	SET @n=1
SET @i=2
SET @result=dateadd(d,1,@d)
WHILE DATEPART(dw,@result)<>@n
	BEGIN
	set @result=dateadd(d,1,@result)
	set @i=@i+1
	END
RETURN  @result
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function NINT(@a float ) 
--Rounds a number to the nearest integer.
returns int
as
BEGIN
return convert(int,round(@a,0))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function NOR(@a int, @b int ) 
--Returns the result of a logical negated OR.
returns int
as
BEGIN
return ~(@a | @b)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function NROOT(@a float, @b float) 
--Returns the n root of a number.
returns float
as
BEGIN
return POWER(@a,1/@b)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function NTODEC(@s VARCHAR(255), @b int) 
--Converts a number on base n to decimal.
returns int
as
BEGIN
DECLARE @i int, @temp char(1), @result int
SELECT @i=1
SELECT @result=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=UPPER(SUBSTRING(@s,@i,1))
IF (@temp>='0') AND (@temp<='9') 
	SELECT @result=@result+ (ASCII(@temp)-48)*POWER(@b,LEN(@s)-@i)
ELSE
	SELECT @result=@result+ (ASCII(@temp)-55)*POWER(@b,LEN(@s)-@i)
SELECT @i=@i+1
END
return @result
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function NUMBERTOWORDS(@n bigint ) 
--Returns the number as words.
returns VARCHAR(255) 
as
BEGIN
DECLARE @i int, @temp char(1),  @s VARCHAR(20), @result VARCHAR(255)
SELECT @s=convert(varchar(20), @n)
SELECT @i=LEN(@s)
SELECT @result=''
WHILE (@i>0)
BEGIN
SELECT @temp=(SUBSTRING(@s,@i,1))
IF ((LEN(@s)-@i) % 3)=1
IF @temp='1'
SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
	WHEN '0' THEN 'ten'
	WHEN '1' THEN 'eleven'
	WHEN '2' THEN 'twelve'
	WHEN '3' THEN 'thirteen'
	WHEN '4' THEN 'fourteen'
	WHEN '5' THEN 'fifteen'
	WHEN '6' THEN 'sixteen'
	WHEN '7' THEN 'seventeen'
	WHEN '8' THEN 'eighteen'
	WHEN '9' THEN 'nineteen'
	END+' '+CASE
			WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
			WHEN ((LEN(@s)-@i)=7) THEN 'million '
			WHEN ((LEN(@s)-@i)=10) THEN 'billion '
			WHEN ((LEN(@s)-@i)=13) THEN 'trillion '
			WHEN ((LEN(@s)-@i)=16) THEN 'quadrillion '
			WHEN ((LEN(@s)-@i)=19) THEN 'quintillion '
			WHEN ((LEN(@s)-@i)=22) THEN 'sextillion '
			WHEN ((LEN(@s)-@i)=25) THEN 'septillion '
			WHEN ((LEN(@s)-@i)=28) THEN 'octillion '
			WHEN ((LEN(@s)-@i)=31) THEN 'nonillion '
			WHEN ((LEN(@s)-@i)=34) THEN 'decillion '
			WHEN ((LEN(@s)-@i)=37) THEN 'undecillion '
			WHEN ((LEN(@s)-@i)=40) THEN 'duodecillion '
			WHEN ((LEN(@s)-@i)=43) THEN 'tredecillion '
			WHEN ((LEN(@s)-@i)=46) THEN 'quattuordecillion '
			WHEN ((LEN(@s)-@i)=49) THEN 'quindecillion '
			WHEN ((LEN(@s)-@i)=52) THEN 'sexdecillion '
			WHEN ((LEN(@s)-@i)=55) THEN 'septendecillion '
			WHEN ((LEN(@s)-@i)=58) THEN 'octodecillion '
			WHEN ((LEN(@s)-@i)=61) THEN 'novemdecillion '
			ELSE ''
			END+@result
ELSE
BEGIN
	SELECT @result=CASE (SUBSTRING(@s,@i+1,1))
		WHEN '0' THEN ''
		WHEN '1' THEN 'one'
		WHEN '2' THEN 'two'
		WHEN '3' THEN 'three'
		WHEN '4' THEN 'four'
		WHEN '5' THEN 'five'
		WHEN '6' THEN 'six'
		WHEN '7' THEN 'seven'
		WHEN '8' THEN 'eight'
		WHEN '9' THEN 'nine'
		END+' '+ CASE
			WHEN ((LEN(@s)-@i)=4) THEN 'thousand '
			WHEN ((LEN(@s)-@i)=7) THEN 'million '
			WHEN ((LEN(@s)-@i)=10) THEN 'billion '
			WHEN ((LEN(@s)-@i)=13) THEN 'trillion '
			WHEN ((LEN(@s)-@i)=16) THEN 'quadrillion '
			WHEN ((LEN(@s)-@i)=19) THEN 'quintillion '
			WHEN ((LEN(@s)-@i)=22) THEN 'sextillion '
			WHEN ((LEN(@s)-@i)=25) THEN 'septillion '
			WHEN ((LEN(@s)-@i)=28) THEN 'octillion '
			WHEN ((LEN(@s)-@i)=31) THEN 'nonillion '
			WHEN ((LEN(@s)-@i)=34) THEN 'decillion '
			WHEN ((LEN(@s)-@i)=37) THEN 'undecillion '
			WHEN ((LEN(@s)-@i)=40) THEN 'duodecillion '
			WHEN ((LEN(@s)-@i)=43) THEN 'tredecillion '
			WHEN ((LEN(@s)-@i)=46) THEN 'quattuordecillion '
			WHEN ((LEN(@s)-@i)=49) THEN 'quindecillion '
			WHEN ((LEN(@s)-@i)=52) THEN 'sexdecillion '
			WHEN ((LEN(@s)-@i)=55) THEN 'septendecillion '
			WHEN ((LEN(@s)-@i)=58) THEN 'octodecillion '
			WHEN ((LEN(@s)-@i)=61) THEN 'novemdecillion '
			ELSE ''
			END+@result
	SELECT @result=CASE @temp
		WHEN '0' THEN ''
		WHEN '1' THEN 'ten'
		WHEN '2' THEN 'twenty'
		WHEN '3' THEN 'thirty'
		WHEN '4' THEN 'fourty'
		WHEN '5' THEN 'fifty'
		WHEN '6' THEN 'sixty'
		WHEN '7' THEN 'seventy'
		WHEN '8' THEN 'eighty'
		WHEN '9' THEN 'ninety'
		END+' '+@result
END
IF (((LEN(@s)-@i) % 3)=2) OR (((LEN(@s)-@i) % 3)=0) AND (@i=1)
BEGIN
SELECT @result=CASE @temp
	WHEN '0' THEN ''
	WHEN '1' THEN 'one'
	WHEN '2' THEN 'two'
	WHEN '3' THEN 'three'
	WHEN '4' THEN 'four'
	WHEN '5' THEN 'five'
	WHEN '6' THEN 'six'
	WHEN '7' THEN 'seven'
	WHEN '8' THEN 'eight'
	WHEN '9' THEN 'nine'
	END +' '+CASE
		WHEN (@s='0') THEN 'zero'
		WHEN (@temp<>'0')AND( ((LEN(@s)-@i) % 3)=2) THEN 'hundred '
		ELSE ''
		END + CASE
		WHEN ((LEN(@s)-@i)=3) THEN 'thousand '
		WHEN ((LEN(@s)-@i)=6) THEN 'million '
		WHEN ((LEN(@s)-@i)=9) THEN 'billion '
		WHEN ((LEN(@s)-@i)=12) THEN 'trillion '
		WHEN ((LEN(@s)-@i)=15) THEN 'quadrillion '
		WHEN ((LEN(@s)-@i)=18) THEN 'quintillion '
		WHEN ((LEN(@s)-@i)=21) THEN 'sextillion '
		WHEN ((LEN(@s)-@i)=24) THEN 'septillion '
		WHEN ((LEN(@s)-@i)=27) THEN 'octillion '
		WHEN ((LEN(@s)-@i)=30) THEN 'nonillion '
		WHEN ((LEN(@s)-@i)=33) THEN 'decillion '
		WHEN ((LEN(@s)-@i)=36) THEN 'undecillion '
		WHEN ((LEN(@s)-@i)=39) THEN 'duodecillion '
		WHEN ((LEN(@s)-@i)=42) THEN 'tredecillion '
		WHEN ((LEN(@s)-@i)=45) THEN 'quattuordecillion '
		WHEN ((LEN(@s)-@i)=48) THEN 'quindecillion '
		WHEN ((LEN(@s)-@i)=51) THEN 'sexdecillion '
		WHEN ((LEN(@s)-@i)=54) THEN 'septendecillion '
		WHEN ((LEN(@s)-@i)=57) THEN 'octodecillion '
		WHEN ((LEN(@s)-@i)=60) THEN 'novemdecillion '
		ELSE ''
			END+ @result
END
SELECT @i=@i-1
END
return REPLACE(@result,'  ',' ')
END











GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function OCTTODEC(@s VARCHAR(255) ) 
--Converts an octal number to decimal.
returns int
as
BEGIN
DECLARE @i int, @temp char(1), @result int
SELECT @i=1
SELECT @result=0
WHILE (@i<=LEN(@s))
BEGIN
SELECT @temp=SUBSTRING(@s,@i,1)
SELECT @result=@result+ (ASCII(@temp)-48)*POWER(8,LEN(@s)-@i)
SELECT @i=@i+1
END
return @result
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function PALINDROME(@n int ) 
--Returns true if the number is a palindrome.
returns bit
as
BEGIN
DECLARE @i int,@bool bit, @s varchar(20)
SET @s=convert(varchar(20),@n)
SET @i=1
SET @bool=1
WHILE (@i<=LEN(@s)/2)
BEGIN
IF SUBSTRING(@s,@i,1)<>SUBSTRING(@s,LEN(@s)-@i+1,1)
	SET @bool=0
SET @i=@i+1
END
RETURN @bool
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function PALINDROMEW( @s varchar(255) ) 
--Returns true if the string is a palindrome.
returns bit
as
BEGIN
DECLARE @i int,@bool bit
SET @i=1
SET @bool=1
WHILE (@i<=LEN(@s)/2)
BEGIN
IF SUBSTRING(@s,@i,1)<>SUBSTRING(@s,LEN(@s)-@i+1,1)
	SET @bool=0
SET @i=@i+1
END
RETURN @bool
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function PENTIUMBUG( ) 
returns bit
as
BEGIN
DECLARE @i float, @j float, @b bit
SET @i=4195835
SET @j=3145727
IF convert(varchar(255),(@i / @j))='1.33382'
	SET @b=0
ELSE
	SET @b=1
RETURN (@b)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function PERFNUMBER(@n int ) 
--Returns the nth perfect number. 
returns bigint
as
BEGIN
RETURN POWER(2,@n-1)*(POWER(2,@n)-1)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function PHI() 
--Returns phi, the "golden ratio".
returns float
as
BEGIN
declare @temp float
select @temp=(1 + SQRT(5))/2 
return @temp
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function PROPERCASE(@s varchar(255)) 
--Returns a string with the first letter of each word at the beginning of a sentence  in uppercase, all other letters in lowercase
returns  varchar(255)
as
BEGIN
DECLARE @i int, @c char(1),@result varchar(255)
SET @result=LOWER(@s)
SET @i=2
SET @result=STUFF(@result,1,1,UPPER(SUBSTRING(@s,1,1)))
WHILE @i<=LEN(@s)
	BEGIN
	SET @c=SUBSTRING(@s,@i,1)
	IF (@c='!') OR (@c='?') OR (@c='_')OR (@c='.')
		IF @i<LEN(@s)
			BEGIN
lblSeek:
			SET @i=@i+1
			IF UPPER(SUBSTRING(@s,@i,1)) LIKE '[A-Z]'
				SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@s,@i,1)))
			ELSE
				IF @i<LEN(@s)
					GOTO lblSeek
			END
	SET @i=@i+1
	END
RETURN  @result
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function RAD2GRAD(@a float ) 
--Converts an angle from radians to grads.
returns float
as
BEGIN
return (@a*PI()/200.0)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function RESETBIT(@a int, @b int ) 
--Resets the value of a certain bit.
returns int
as
BEGIN
return @a | ~(POWER(2,@b))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function REWRAP(@s varchar(255), @n int ) 
--Returns a string s wrapped in blocks of i characters, removing previous wrapping. 
returns varchar(255)
as
BEGIN
DECLARE @t VARCHAR(255), @i int
SET @i=1
SET @t=''
SET @s=REPLACE(@s,CHAR(10),'')
SET @s=REPLACE(@s,CHAR(13),'')
WHILE @i<=LEN(@s)
	BEGIN
	SET @t=@t+substring(@s,@i,1)
	IF (@i % @n)=0
		SET @t=@t+CHAR(13)
	SET @i=@i+1
	END
RETURN  @t
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function ROMAN2A(@s VARCHAR(20)) 
--Converts an roman numeral to arabic.
returns int
as
BEGIN
declare @f int, @k int, @z int, @z1 int
select @f=LEN(@s)
select @k=0
select @z1=0
WHILE (@f>0)
BEGIN
	select @z = CASE UPPER(SUBSTRING(@s,@f,1))
	WHEN 'I' THEN 1
	WHEN 'V' THEN 5
	WHEN 'X' THEN 10
	WHEN 'L' THEN 50
	WHEN 'C' THEN 100
	WHEN 'D' THEN 500
	WHEN 'M' THEN 1000
	END
IF @z1>@z
	SELECT @k=@k-@z
ELSE
	BEGIN
	SELECT @k=@k+@z
	SELECT @z1=@z
	END
select @f=@f-1
END
return @k
END







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function RPAD(@s varchar(255), @n int, @p varchar(255) ) 
--Returns a string s1 right-padded to length i with a sequence of characters s2. 
returns varchar(255)
as
BEGIN
return @s+REPLICATE(@p,@n)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function SEC(@a float ) 
--Returns the trigonometric secant of the given angle (in radians) in the given expression.
returns float
as
BEGIN
return (1/COS(@a))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function SECH(@a float ) 
--Returns the hyperbolic secant of a number.
returns float
as
BEGIN
return 2/( POWER(dbo.E(),@a) +  POWER(dbo.E(),-@a) )
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function SETBIT(@a int, @b int ) 
--Sets the value of a certain bit.
returns int
as
BEGIN
return @a | (POWER(2,@b))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function SHIFTLEFT(@a int, @b int ) 
--Returns a number shifted to the left.
returns int
as
BEGIN
return @a * POWER(2, @b)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function SHIFTRIGHT(@a int, @b int ) 
--Returns a number shifted to the right.
returns int
as
BEGIN
return @a / POWER(2, @b)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function SINH(@a float ) 
--Returns the hyperbolic sine of a number.
returns float
as
BEGIN
return ( POWER(dbo.E(),@a) -  POWER(dbo.E(),-@a) )/2
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function SLOPE(@x1 float,@y1 float, @x2 float,@y2 float) 
--Returns the slope of a line define by 2 points P(f1, f2) and T(f3, f4). 
returns float
as
BEGIN
return (@y2-@y1)/(@x2-@x1)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function SQRTPI(@a float ) 
--Returns the square root of (number * Pi).
returns float
as
BEGIN
return SQRT(@a*PI())
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function STRIPL(@s varchar(255) ) 
--Returns the left side of half of the string. 
returns varchar(255)
as
BEGIN
return LEFT(@s, LEN(@s)/2)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function STRIPR(@s varchar(255) ) 
--Returns the right side of half of the string. 
returns varchar(255)
as
BEGIN
DECLARE @i int
SET @i=LEN(@s)-LEN(@s)/2
return RIGHT(@s, @i)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE function SUMALIQUOT (@n bigint ) 
--Returns the sum of all aliquots from i
returns bigint
as
BEGIN
DECLARE @i bigint, @j bigint
SET @i=1
SET @j=0
WHILE @i<=@n/2
BEGIN
IF (@n % @i)=0
	SET @j=@j+@i
SET @i=@i+1
END
RETURN @j
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function SUMSEQ(@n bigint) 
--Returns the summation of all integers from 1 to n.
returns bigint
as
BEGIN
    return (@n+@n*@n)/2
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function TANH(@a float ) 
--Returns the hyperbolic tangent of a number.
returns float
as
BEGIN
return (dbo.SINH(@a)/dbo.COSH(@a))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function TRANSLATE( @s varchar(255), @f varchar(255), @t varchar(255) ) 
returns varchar(255)
as
BEGIN
DECLARE @i int, @j int, @c char(1),@result varchar(255)
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
	BEGIN
	SET @c=SUBSTRING(@s,@i,1)
	SET @j=CHARINDEX(@c,@f)
	IF @j>0
		SET @result=@result + SUBSTRING(@t,@j,1)
	SET @i=@i+1
	END
RETURN @result
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function TRIM(@s VARCHAR(255) ) 
--Returns a string removing spaces at both ends.
returns  VARCHAR(255) 
as
BEGIN
return RTRIM(LTRIM(@s))
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function TRUNC(@a float ) 
--Returns a number truncated to an integer.
returns int
as
BEGIN
return convert(int,@a)
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function UNWRAP(@s varchar(255)) 
--Returns a string removing all wrapping. 
returns varchar(255)
as
BEGIN
SET @s=REPLACE(@s,CHAR(10),'')
SET @s=REPLACE(@s,CHAR(13),'')
RETURN @s
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function VAL(@a VARCHAR(50) ) 
--Returns a numeric value from a string, it is the opposite of STR.
returns float
as
BEGIN
declare @temp float
if (ISNUMERIC(@a)=1)
	select @temp=convert(float,@a)
ELSE
	select @temp=0
return @temp
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function VALIDEMAIL(@s varchar(255)) 
--Returns true if the string is a valid email address.
returns bit
as
BEGIN
DECLARE @u VARCHAR(60), @v VARCHAR(60), @x VARCHAR(60), @i int, @j int, @result bit
SET @result=1
SET @i=CHARINDEX('@',@s)
SET @u=LEFT(@s,@i-1)
SET @j=dbo.CHARINDEXREV('.',@s)
SET @v=RIGHT(@s,LEN(@s)-@j)
SET @x=substring(@s,@i+1,@j-@i-1)
IF LEN(@x)<3
	BEGIN
	SET @result=0
	GOTo done
	END
IF (LEN(@x)=3) AND (@x NOT LIKE '[a-z,A-Z][a-z,A-Z][a-z,A-Z]')
	BEGIN
	SET @result=0
	GOTo done
	END
IF (LEN(@x)=2) AND (@x NOT LIKE '[a-z,A-Z][a-z,A-Z]')
	BEGIN
	SET @result=0
	GOTo done
	END
SET @i=1
WHILE (@i<LEN(@u))
	BEGIN
	IF SUBSTRING(@u,@i,1) NOT LIKE '[a-z,A-Z,0-9,_,-,.]'
		BEGIN
		SET @result=0
		GOTo done
		END
	SET @i=@i+1
	END
SET @i=1
WHILE (@i<LEN(@v))
	BEGIN
	IF SUBSTRING(@v,@i,1) NOT LIKE '[a-z,A-Z,0-9,_,-,.]'
		BEGIN
		SET @result=0
		GOTo done
		END
	SET @i=@i+1
	END
done:
return @result
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function VALIDIP(@s varchar(15)) 
--Returns true if the string is a IP.
returns bit
as
BEGIN
DECLARE @u VARCHAR(3), @v VARCHAR(3), @x VARCHAR(3),@y VARCHAR(3), @i int, @j int, @result bit
SET @result=1
IF dbo.INCLUDED('.',@s)<>3
	BEGIN
	SET @result=0
	GOTO done
	END
SET @i=CHARINDEX('.',@s)
SET @u=LEFT(@s,@i-1)
SET @j=CHARINDEX('.',@s,@i+1)
SET @v=substring(@s,@i+1,@j-@i-1)
SET @i=CHARINDEX('.',@s,@j+1)
SET @x=substring(@s,@j+1,@i-@j-1)
SET @y=substring(@s,@i+1,LEN(@s)-@i)
IF ISNUMERIC(@u)=0 OR ISNUMERIC(@v)=0 OR ISNUMERIC(@x)=0 OR ISNUMERIC(@y)=0
	BEGIN
	SET @result=0
	GOTo done
	END
IF (CONVERT(INT, @u)<0) OR  (CONVERT(INT, @v)<0) OR  (CONVERT(INT, @x)<0)  OR  (CONVERT(INT, @y)<0) 
	BEGIN
	SET @result=0
	GOTo done
	END
IF (CONVERT(INT, @u)>255) OR  (CONVERT(INT, @v)>255) OR  (CONVERT(INT, @x)>255)  OR  (CONVERT(INT, @y)>255) 
	BEGIN
	SET @result=0
	GOTo done
	END
done:
RETURN  @result
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function VALIDZIP(@s varchar(5)) 
--Returns true if the string is a valid zip code.
returns bit
as
BEGIN
DECLARE @result bit
IF @s LIKE '[1-9][0-9][0-9][0-9][0-9]'
SET @s=1
ELSE
SET @s=0
return @result
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function VALIDZIP9(@s varchar(10)) 
--Returns true if the string is a valid zip code 5+4.
returns bit
as
BEGIN
DECLARE @result bit
IF @s LIKE '[1-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]'
SET @s=1
ELSE
SET @s=0
return @result
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function WORDCOUNT(@s varchar(255)) 
--Returns the number of words from string s. 
returns INT
as
BEGIN
DECLARE @i INT
SET @s=REPLACE(@s,CHAR(10),' ')
SET @s=REPLACE(@s,CHAR(13),' ')
SET @s=REPLACE(@s,'0','')
SET @s=REPLACE(@s,'1','')
SET @s=REPLACE(@s,'2','')
SET @s=REPLACE(@s,'3','')
SET @s=REPLACE(@s,'4','')
SET @s=REPLACE(@s,'5','')
SET @s=REPLACE(@s,'6','')
SET @s=REPLACE(@s,'7','')
SET @s=REPLACE(@s,'8','')
SET @s=REPLACE(@s,'9','')
SET @s=REPLACE(@s,'!',' ')
SET @s=REPLACE(@s,';',' ')
SET @s=REPLACE(@s,':',' ')
SET @s=REPLACE(@s,'[',' ')
SET @s=REPLACE(@s,']',' ')
SET @s=REPLACE(@s,'+',' ')
SET @s=REPLACE(@s,'{',' ')
SET @s=REPLACE(@s,'}',' ')
SET @s=REPLACE(@s,'&','')
SET @s=REPLACE(@s,'.',' ')
SET @s=REPLACE(@s,',',' ')
SET @s=REPLACE(@s,'?',' ')
SET @s=REPLACE(@s,'/',' ')
SET @s=REPLACE(@s,'_',' ')
SET @s=REPLACE(@s,'-','')
SET @s=REPLACE(@s,'(',' ')
SET @s=REPLACE(@s,')',' ')
SET @s=REPLACE(@s,'''','')
SET @s=REPLACE(@s,'"',' ')
WHILE CHARINDEX ('  ',@s)>0
	SET @s=REPLACE(@s,'  ',' ')
SET @s=RTRIM(LTRIM(@s))
SET @i=dbo.INCLUDED(' ',@s)+1
RETURN @i
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function WRAP(@s varchar(255), @n int ) 
--Returns a string s wrapped in blocks of i characters. 
returns varchar(255)
as
BEGIN
DECLARE @t VARCHAR(255), @i int
SET @i=1
SET @t=''
WHILE @i<=LEN(@s)
	BEGIN
	SET @t=@t+substring(@s,@i,1)
	IF (@i % @n)=0
		SET @t=@t+CHAR(13)
	SET @i=@i+1
	END
RETURN  @t
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE function XORCHAR( @s VARCHAR(255), @x tinyint ) 
--Returns a string encrypted/decrypted with key t ( XOR encryption )
returns VARCHAR(255)
as
BEGIN
DECLARE @result VARCHAR(255), @i int, @temp tinyint
SET @i=1
SET @result=''
WHILE @i<=LEN(@s)
	BEGIN
	SET @temp=ASCII(SUBSTRING(@s,@i,1))
	SET @result=@result + CHAR(@temp ^ @x)
	SET @i=@i+1
	END
RETURN @result
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO