Functions.sql ( File view )

  • By daragon 2014-11-12
  • View(s):18
  • Download(s):0
  • Point(s): 1
			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
...
...
(Not finished, please download and read the complete file)
			
...
Expand> <Close

Want complete source code? Download it here

Point(s): 1

Download
0 lines left, continue to read
Sponsored links

File list

Tips: You can preview the content of files by clicking file names^_^
Name Size Date
01.97 kB
Examples.pdf112.63 kB2014-05-03 11:12
Functions.sql60.95 kB2014-05-03 11:12
README.md93.00 B2014-05-03 11:12
TSQL103.65 kB2014-05-03 11:12
functions.txt13.63 kB2014-05-03 11:12
...
Sponsored links

Functions.sql (170.26 kB)

Need 1 point
Your Point(s)

Your Point isn't enough.

Get point immediately by PayPal

More(Debit card / Credit card / PayPal Credit / Online Banking)

Submit your source codes. Get more point

LOGIN

Don't have an account? Register now
Need any help?
Mail to: support@codeforge.com

切换到中文版?

CodeForge Chinese Version
CodeForge English Version

Where are you going?

^_^"Oops ...

Sorry!This guy is mysterious, its blog hasn't been opened, try another, please!
OK

Warm tip!

CodeForge to FavoriteFavorite by Ctrl+D