*/
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)) -----------‘acthebcsex’
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
,@pos2 ,@split varchar(10),@splitlen SELECT TOP 1
@pos1=1,@split=split,@splitlen=splitlen
FROM @splits
WHERE @s LIKE split+'%' ----------------判断在串中有无分隔符
WHILE @pos1>0 -----------在串中有分隔符
CIRCLEBEGIN
SELECT TOP 1
@pos2=CHARINDEX(split,@s,@splitlen+1) ----------@pos2=6
FROM @splits
WHERE CHARINDEX(split,@s,@splitlen+1)>0 ----------------在串中要存在 >=2分隔符
ORDER BY CHARINDEX(split,@s,@splitlen+1)
IF @@ROWCOUNT=0 -------------只有
个分隔符时如‘acthe’BEGIN
INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,'')) --------------@re(ac,the)
RETURN
END
ELSE ----------------------------如有2个以上分隔符时@s='acthebcsex'
BEGIN
---------@re(ac,sub
('acthebcsex',3,6-3))=(ac,the)INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1)) ---
步SELECT TOP 1
---------------------重新送值
@pos1=1,重新进入外面CIRCLE@split=bc,@s='bcsex' , 又从@pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')-------- 2步
FROM @splits
WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%' ----------新子串存在分隔符while>0开始运行,判断
下执行-----------
步:@re(bc,sex)END
END
RETURN
END
GO
1
@s='acthebcsex'结果为；'ac','the' 'bc','sex'2,我以前
认识以为@split会每次变化实际上它是表变理任个都可以
3
while 运行下从外到里从里到外又要判断不成立退出加个
就可以了4
insert tablename
select * from tablename 2者结果集必须致才可以传递5
多多学习多重CIRCLE练习6,谢啦邹老大
算法Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1297938
篇文章: Eclipse是如何连接Oracle数据库篇文章: 黑客:杀手跨站ASP Script攻击
最新评论