数据库中各种字符串的截取函数

　　今天看见有人再问数据库中的数据截取了，我就和大家分享一下我看过的一篇有关这方面的文章吧。

　　

  1 --各种字符串截取函数
  2  
  3 if exists (select * from dbo.ss where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
  4 drop function [dbo].[f_splitSTR]
  5 GO
  6  
  7 --1 循环截取法
  8 CREATE FUNCTION f_splitSTR(
  9 @s   varchar(8000),   --待分拆的字符串
 10 @split varchar(10)     --数据分隔符
 11 )RETURNS @re TABLE(col varchar(100))
 12 AS
 13 BEGIN
 14     DECLARE @splitlen int
 15     SET @splitlen=LEN(@split+'a')-2
 16     WHILE CHARINDEX(@split,@s)>0
 17     BEGIN
 18         INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
 19         SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
 20     END
 21     INSERT @re VALUES(@s)
 22     RETURN
 23 END
 24 GO
 25  
 26  
 27 /*==============================================*/
 28  
 29 if exists (select * from dbo.ss where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
 30 drop function [dbo].[f_splitSTR]
 31 GO
 32  
 33 --2 使用临时性分拆辅助表法
 34 CREATE FUNCTION f_splitSTR(
 35 @s   varchar(8000),  --待分拆的字符串
 36 @split varchar(10)     --数据分隔符
 37 )RETURNS @re TABLE(col varchar(100))
 38 AS
 39 BEGIN
 40     --创建分拆处理的辅助表(用户定义函数中只能操作表变量)
 41     DECLARE @t TABLE(ID int IDENTITY,b bit)
 42     INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
 43  
 44     INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
 45     FROM @t
 46     WHERE ID<=LEN(@s+'a') 
 47         AND CHARINDEX(@split,@split+@s,ID)=ID
 48     RETURN
 49 END
 50 GO
 51  
 52 /*==============================================*/
 53  
 54 if exists (select * from dbo.ss where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
 55 drop function [dbo].[f_splitSTR]
 56 GO
 57  
 58 if exists (select * from dbo.ss where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
 59 drop table [dbo].[tb_splitSTR]
 60 GO
 61  
 62 --2.1 使用永久性分拆辅助表法
 63 --字符串分拆辅助表
 64 SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
 65 FROM syscolumns a,syscolumns b
 66 GO
 67  
 68 --字符串分拆处理函数
 69 CREATE FUNCTION f_splitSTR(
 70 @s     varchar(8000),  --待分拆的字符串
 71 @split  varchar(10)     --数据分隔符
 72 )RETURNS TABLE
 73 AS
 74 RETURN(
 75     SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
 76     FROM tb_splitSTR
 77     WHERE ID<=LEN(@s+'a') 
 78         AND CHARINDEX(@split,@split+@s,ID)=ID)
 79 GO
 80  
 81  
 82 /*==============================================*/
 83  
 84 if exists (select * from dbo.ss where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
 85 drop function [dbo].[f_splitSTR]
 86 GO
 87  
 88 --3 将数据项按数字与非数字再次拆份
 89 CREATE FUNCTION f_splitSTR(
 90 @s   varchar(8000),    --待分拆的字符串
 91 @split varchar(10)     --数据分隔符
 92 )RETURNS @re TABLE(No varchar(100),Value varchar(20))
 93 AS
 94 BEGIN
 95     --创建分拆处理的辅助表(用户定义函数中只能操作表变量)
 96     DECLARE @t TABLE(ID int IDENTITY,b bit)
 97     INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b
 98  
 99     INSERT @re 
100     SELECT    No=REVERSE(STUFF(col,1,PATINDEX('%[^-^.^0-9]%',col+'a')-1,'')),
101         Value=REVERSE(LEFT(col,PATINDEX('%[^-^.^0-9]%',col+'a')-1))
102     FROM(
103         SELECT col=REVERSE(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID))
104         FROM @t
105         WHERE ID<=LEN(@s+'a') 
106             AND CHARINDEX(@split,@split+@s,ID)=ID)a
107     RETURN
108 END
109 GO
110  
111  
112 /*==============================================*/
113  
114 if exists (select * from dbo.ss where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
115 drop function [dbo].[f_splitSTR]
116 GO
117  
118 --3.1 分拆短信数据
119 CREATE FUNCTION f_splitSTR(@s varchar(8000))
120 RETURNS @re TABLE(split varchar(10),value varchar(100))
121 AS
122 BEGIN
123     DECLARE @splits TABLE(split varchar(10),splitlen as LEN(split))
124     INSERT @splits(split)
125     SELECT 'AC' UNION ALL
126     SELECT 'BC' UNION ALL
127     SELECT 'CC' UNION ALL
128     SELECT 'DC'   
129     DECLARE @pos1 int,@pos2 int,@split varchar(10),@splitlen int
130     SELECT TOP 1 
131         @pos1=1,@split=split,@splitlen=splitlen
132     FROM @splits
133     WHERE @s LIKE split+'%'
134     WHILE @pos1>0
135     BEGIN
136         SELECT TOP 1
137             @pos2=CHARINDEX(split,@s,@splitlen+1)
138         FROM @splits
139         WHERE CHARINDEX(split,@s,@splitlen+1)>0
140         ORDER BY CHARINDEX(split,@s,@splitlen+1)
141         IF @@ROWCOUNT=0
142         BEGIN
143             INSERT @re VALUES(@split,STUFF(@s,1,@splitlen,''))
144             RETURN
145         END
146         ELSE
147         BEGIN
148             INSERT @re VALUES(@split,SUBSTRING(@s,@splitlen+1,@pos2-@splitlen-1))
149             SELECT TOP 1 
150                 @pos1=1,@split=split,@splitlen=splitlen,@s=STUFF(@s,1,@pos2-1,'')
151             FROM @splits
152             WHERE STUFF(@s,1,@pos2-1,'') LIKE split+'%'
153         END
154     END
155     RETURN
156 END
157 GO
View Code

 　　在增加知识的同时，也希望能帮助有需要的盆友，同时也希望大家共同进步，互相学习。

posted @ 2013-09-17 10:26  妍珊  阅读(4722)  评论(0)  编辑  收藏  举报