--各種字符串分函數

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

?

各種字符串分拆處理函數.sql