笔记249 SQLSERVER自定义函数实现split功能 2013-4-25
1 --SQLSERVER自定义函数实现split功能 2013-4-25
2 --http://blog.chinaunix.net/uid-584462-id-2733085.html
3 --http://social.technet.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/29724c5a-adb6-4a2c-9003-86ead071c059
4
5 USE [tempdb]
6 GO
7 -------------------------------------------------
8 --实现split功能 的函数
9 --date :2005-4-20
10 --Author :Domino
11 ---------------------------------------------------
12 DROP FUNCTION [dbo].[f_split]
13 GO
14 CREATE FUNCTION f_split
15 (
16 @SourceSql VARCHAR(8000) ,
17 @StrSeprate VARCHAR(10)
18 )
19 RETURNS @temp TABLE ( str VARCHAR(100) )
20 AS
21 BEGIN
22 DECLARE @i INT
23 SET @SourceSql = RTRIM(LTRIM(@SourceSql))
24 SET @i = CHARINDEX(@StrSeprate, @SourceSql)
25 WHILE @i >= 1
26 BEGIN
27 INSERT @temp
28 VALUES ( LEFT(@SourceSql, @i - 1) )
29 SET @SourceSql = SUBSTRING(@SourceSql, @i + 1,
30 LEN(@SourceSql) - @i)
31 SET @i = CHARINDEX(@StrSeprate, @SourceSql)
32 END
33 IF @SourceSql <> '\'
34 INSERT @temp
35 VALUES ( @SourceSql )
36 RETURN
37 END
38 --实验：
39 USE tempdb
40 go
41 CREATE TABLE #tempA (ID varchar(20),str varchar(20))
42 INSERT INTO #tempA
43 SELECT '1001','A,B,C,D'
44
45
46 DECLARE @str VARCHAR(50)
47 DECLARE @ID INT
48 SELECT @str=a.[str],@ID=A.[ID] FROM #tempA AS a
49
50 SELECT @ID AS ID,* FROM dbo.[f_split](@str,',')
