1. 删除字符串中符合正规表达的字符
CREATE FUNCTION fun_regex_del
(
@inStr VARCHAR(MAX),
@expression VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @str_userlist VARCHAR(MAX);
SET @str_userlist = @inStr;
DECLARE @i INT;
SET @i = 0;
WHILE PATINDEX(@expression,@str_userlist) > 0
BEGIN
SET @i = PATINDEX(@expression,@str_userlist)
SET @str_userlist = STUFF(@str_userlist,@i,1,'')
END
RETURN @str_userlist;
END
GO
2. URLDecode
CREATE FUNCTION [dbo].[SP_fnURLDecode] ( @input NVARCHAR(4000) )
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @char NVARCHAR(4)
DECLARE @asc NVARCHAR(4)
DECLARE @asc2 NVARCHAR(4)
WHILE ( CHARINDEX('%', @input) > 0 )
BEGIN
SET @char = ( SELECT SUBSTRING(@input,
CHARINDEX('%', @input) + 1,
2)
)
IF ( ISNUMERIC(SUBSTRING(@char, 1, 1)) ) > 0
BEGIN
SET @asc = ( SELECT CAST(SUBSTRING(@char, 1, 1) AS INT)
) * 16
END
ELSE
BEGIN
SET @asc = ( SELECT ASCII(CAST(SUBSTRING(@char, 1, 1) AS CHAR))
) - 55
SET @asc = ( SELECT @asc * 16
)
END
IF ( ISNUMERIC(SUBSTRING(@char, 2, 1)) ) > 0
SET @asc = ( SELECT CAST(@asc AS INT)
+ ( SELECT CAST(SUBSTRING(@char, 2, 1) AS INT)
)
)
ELSE
BEGIN
SET @asc2 = ( SELECT ASCII(CAST(SUBSTRING(@char, 2,
1) AS CHAR))
) - 55
SET @asc = ( SELECT CAST(@asc AS INT)
+ ( SELECT CAST(@asc2 AS INT)
)
)
END
SET @input = ( SELECT SUBSTRING(@input, 0,
CHARINDEX('%', @input))
) + CHAR(@asc)
+ ( SELECT SUBSTRING(@input, CHARINDEX('%', @input) + 3,
LEN(@input))
)
END
RETURN @input
END
GO