17.各种字符串分拆处理函数

待我称王封你为后i 2024-02-19 17:45 17阅读 0赞

--各种字符串分函数
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[f_splitSTR]
    GO
    
    --3.2.1 循环截取法
    CREATE FUNCTION f_splitSTR(
    @s   varchar(8000),   --待分拆的字符串
    @split varchar(10)     --数据分隔符
    )RETURNS @re TABLE(col varchar(100))
    AS
    BEGIN
    	DECLARE @splitlen int
    	SET @splitlen=LEN(@split+'a')-2
    	WHILE CHARINDEX(@split,@s)>0
    	BEGIN
    		INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
    		SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
    	END
    	INSERT @re VALUES(@s)
    	RETURN
    END
    GO
    
    
    /*==============================================*/
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[f_splitSTR]
    GO
    
    --3.2.3.1 使用临时性分拆辅助表法
    CREATE FUNCTION f_splitSTR(
    @s   varchar(8000),  --待分拆的字符串
    @split varchar(10)     --数据分隔符
    )RETURNS @re TABLE(col varchar(100))
    AS
    BEGIN
    	--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
    	DECLARE @t TABLE(ID int IDENTITY,b bit)
    	INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
    
    	INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
    	FROM @t
    	WHERE ID<=LEN(@s+'a') 
    		AND CHARINDEX(@split,@split+@s,ID)=ID
    	RETURN
    END
    GO
    
    /*==============================================*/
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[f_splitSTR]
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
    drop table [dbo].[tb_splitSTR]
    GO
    
    --3.2.3.2 使用永久性分拆辅助表法
    --字符串分拆辅助表
    SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
    FROM syscolumns a,syscolumns b
    GO
    
    --字符串分拆处理函数
    CREATE FUNCTION f_splitSTR(
    @s     varchar(8000),  --待分拆的字符串
    @split  varchar(10)     --数据分隔符
    )RETURNS TABLE
    AS
    RETURN(
    	SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
    	FROM tb_splitSTR
    	WHERE ID<=LEN(@s+'a') 
    		AND CHARINDEX(@split,@split+@s,ID)=ID)
    GO
    
    
    /*==============================================*/
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[f_splitSTR]
    GO
    
    --3.2.5 将数据项按数字与非数字再次拆份
    CREATE FUNCTION f_splitSTR(
    @s   varchar(8000),    --待分拆的字符串
    @split varchar(10)     --数据分隔符
    )RETURNS @re TABLE(No varchar(100),Value varchar(20))
    AS
    BEGIN
    	--创建分拆处理的辅助表(用户定义函数中只能操作表变量)
    	DECLARE @t TABLE(ID int IDENTITY,b bit)
    	INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
    
    	INSERT @re 
    	SELECT	No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),
    		Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))
    	FROM(
    		SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
    		FROM @t
    		WHERE ID<=LEN(@s+'a') 
    			AND CHARINDEX(@split,@split+@s,ID)=ID)a
    	RETURN
    END
    GO
    
    
    /*==============================================*/
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[f_splitSTR]
    GO
    
    --3.2.6 分拆短信数据
    CREATE FUNCTION f_splitSTR(@s varchar(8000))
    RETURNS @re TABLE(split varchar(10),value varchar(100))
    AS
    BEGIN
    	DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split))
    	INSERT @splits(split)
    	SELECT 'AC' UNION ALL
    	SELECT 'BC' UNION ALL
    	SELECT 'CC' UNION ALL
    	SELECT 'DC'	
    	DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int
    	SELECT TOP 1 
    		@pos1=1,@split=split,@splitlen=splitlen
    	FROM @splits
    	WHERE @s LIKE split+'%'
    	WHILE @pos1>0
    	BEGIN
    		SELECT TOP 1
    			@pos2=CHARINDEX(split,@s,@splitlen+1)
    		FROM @splits
    		WHERE CHARINDEX(split,@s,@splitlen+1)>0
    		ORDER BY CHARINDEX(split,@s,@splitlen+1)
    		IF @@ROWCOUNT=0
    		BEGIN
    			INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,''))
    			RETURN
    		END
    		ELSE
    		BEGIN
    			INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))
    			SELECT TOP 1 
    				@pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')
    			FROM @splits
    			WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%'
    		END
    	END
    	RETURN
    END
    GO