Sqlserver 自定义实用函数集(不定期更新)

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)) ) > 0 BEGIN SET @asc = ( SELECT CAST(SUBSTRING(@char,1) AS INT) ) * 16 END ELSE BEGIN SET @asc = ( SELECT ASCII(CAST(SUBSTRING(@char,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,1) AS INT) ) ) ELSE BEGIN SET @asc2 = ( SELECT ASCII(CAST(SUBSTRING(@char,1) AS CHAR)) ) - 55 SET @asc = ( SELECT CAST(@asc AS INT) + ( SELECT CAST(@asc2 AS INT) ) ) END SET @input = ( SELECT SUBSTRING(@input,0,@input)) ) + CHAR(@asc) + ( SELECT SUBSTRING(@input,@input) + 3,LEN(@input)) ) END RETURN @input END GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!