SQL Custom Functions Daquan

CREATE FUNCTION [dbo]. [F_addyears]
(
---increase year
@currentDay DATETIME,
@years INT
)
RETURNS DATETIME
As
BEGIN
RETURN DATEADD (year, @years, @currentDay)
END

--Increase month
CREATE FUNCTION [dbo]. [F_addmonths]
(
---increase month
@currentDay DATETIME,
@months INT
)
RETURNS DATETIME
As
BEGIN
RETURN DATEADD (month, @months, @currentDay)
END

--Increase Day
CREATE FUNCTION [dbo]. [F_adddays]
(
---increase day
@currentDay DATETIME,
@days INT
)
RETURNS DATETIME
As
BEGIN
RETURN DATEADD (Day, @days, @currentDay)
END

----increase the number of seconds
CREATE FUNCTION [dbo]. [F_addseconds]
(
---Increase the number of seconds
@currentDay DATETIME,
@seconds INT
)
RETURNS DATETIME
As
BEGIN
RETURN DATEADD (SS, @seconds, @currentDay)
END

---Return to date section
CREATE FUNCTION [dbo]. [F_getdate] (---Return date part
@currentDay DATETIME)
RETURNS DATETIME
As
BEGIN
RETURN CONVERT (Datetime,convert (CHAR), @currentDay, 112))
END

--Returns the end time of the date
CREATE FUNCTION [dbo]. [F_getendtime] (---The end time of the return date
@currentDay DATETIME)
RETURNS DATETIME
As
BEGIN
RETURN to dbo. [F_addseconds] (Dbo.f_getdate (@currentDay), 24*60*60-1)
END

--string splitting
CREATE FUNCTION [dbo].[ F_split]
(
@string varchar (MAX),
@separator CHAR
)
RETURNS @return TABLE (value VARCHAR)
as
BEGIN
DECLARE @len INT
SET @len = Len (@string)
IF (SUBSTRING (@string, @len, 1) <> @separator) SET @string = @string + @separator
DECLARE @strtemp VARCHAR
DECLARE @index1 INT
DECLARE @index2 int Set @index1 = 0
Set @index2 = CHARINDEX (@separator, @string, 1)

while @index2 <> 0
BEGIN
SE T @strtemp = SUBSTRING (@string, @index1 + 1,
@index2-@index1-1)
INSERT @return
([value])
VALUES (@s trtemp)
Set @index1 = @index2
Set @index2 = CHARINDEX (@separator, @string, @index1 + 1)
END
RETURN
EN D

--MD5 encryption
CREATE FUNCTION [dbo]. [F_MD5]
(
@string VARCHAR (MAX),--Encrypt string
@WhatBit INT--Number of digits
)
RETURNS VARCHAR (50)
As
BEGIN
DECLARE @order VARCHAR (50)
SET @order = Right (Sys.fn_varbintohexstr (hashbytes (' MD5 ', CAST (@string as VARCHAR (MAX))), @WhatBit);
RETURN (@order)-The post-split string
END

---get string after
CREATE FUNCTION [dbo]. [F_splitstring]
(
@string VARCHAR (MAX),--the string to be split
@splitChar VARCHAR (10)--Split character
)
RETURNS VARCHAR (20)
As
BEGIN
DECLARE @order VARCHAR (20)
SET @order = SUBSTRING (@string,
CHARINDEX (@splitChar, @string) + LEN (@splitChar),
LEN (@string)-CHARINDEX (@splitChar, @string)
-(LEN (@splitChar)-1));
RETURN (@order)-The post-split string
END

--Escape character conversion
CREATE FUNCTION [dbo].[ Fun_replacekey] (@key VARCHAR (max))
RETURNS varchar (max)
as
BEGIN
SET @key = REPLACE (@key, ' < ', ' < '
Set @key = replace (@key, ' <= ', ' <= ')
Set @key = replace (@key, ' > ', ' > ')
Set @key = replace (@key, ' >= ', ' >= ')
Set @key = replace (@key, ' & ', ' ")
Set @key = replace (@key, ' ' ', ' \ ')
Set @key = REPL ACE (@key, ', ' \ r \ n ')
RETURN @key
END

---Get the number of split strings
CREATE FUNCTION [dbo].[ Getsplitlength]
(
@String NVARCHAR (MAX),--the string to be split
@Split NVARCHAR (10)--delimiter symbol
)
RETURNS INT
as
BEGIN
DECLARE @location int
DECLARE @start int
DECLARE @length int
SET @String = LTRIM (RTRIM (@String)) Set @location = CHARINDEX (@split, @String)
Set @length = 1
While @location <> 0
BEGIN
Set @start = @location + 1
Set @location = CHARINDEX (@split, @String, @start)
Set @length = @length + 1
END
RETURN @ Length
END

--Get the positional character of the split string
CREATE FUNCTION [dbo]. [Getsplitofindex]
(
@String NVARCHAR (MAX),--the string to split
@split NVARCHAR (10),--Separator symbol
@index INT--Take the first few elements
)
RETURNS NVARCHAR (1024)
As
BEGIN
DECLARE @location INT
DECLARE @start INT
DECLARE @next INT
DECLARE @seed INT

SET @String = LTRIM (RTRIM (@String))
SET @start = 1
SET @next = 1
SET @seed = LEN (@split)

SET @location = CHARINDEX (@split, @String)
While @location <> 0
and @index > @next
BEGIN
SET @start = @location + @seed
SET @location = CHARINDEX (@split, @String, @start)
SET @next = @next + 1
END
IF @location = 0
SELECT @location = LEN (@String) + 1

RETURN SUBSTRING (@String, @start, @[email protected])
END

SQL Custom Functions Daquan