今天看见有人再问数据库中的数据截取了,我就和大家分享一下我看过的一篇有关这方面的文章吧。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
在增加知识的同时,也希望能帮助有需要的盆友,同时也希望大家共同进步,互相学习。